Bismillaahirrahmaanirrahiim.. Assalaamu’alaykum warahmatullaahi wabarakaatuh.
Halo sobat keren, kali ini, mari kita pelajari bersama, bagaimana dengan 33 baris kode (atau mungkin kurang dari itu), untuk membersihkan data dan membuat rekapitulasi data PIPK dengan memanfaatkan bahasa M 🙂
Fitur (sementara yang keinget dulu aja saya coba cantumkan):
- Versi cepat membedakan konten kolom numeric maupun non-numeric dengan kata kunci huruf vokal (a, i, u, e, o), dan tanda baca (spasi, koma, titik, dash, dsb.)
- Menambahkan kolom Nomor Index dari file-file asli. Menurut saya ini cukup penting untuk mencegah duplikasi baris. Karena sebelumnya pernah kejadian, beberapa baris data yang diquery muncul 2 sampai 3 kali.
- Masih terbuka ruang untuk improvisasi lebih lanjut
Workflow:
- Filter dan Download file-file yang telah terkumpul di Google Drive (Bisa menggunakan cara simpel dengan formula untuk menggenerate commandnya)
- Power Query dengan kode berikut:
Kode dalam bahasa M:
(Sstt.. Tidak perlu terdistraksi dengan nama-nama kolom yang panjang dan unik, yah!)
let
Source = Folder.Files("D:\PIPK 2023_10"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom","D:\PIPK 2023\","",Replacer.ReplaceText,{"Folder Path"}),
#"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Name", "Date created", "Folder Path", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Item] = "1. Tabel A-JIJ" or [Item] = "TABEL A" or [Item] = "Tabel A" or [Item] = "Tabel A ")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name", "Date created", "Folder Path", "Data"}),
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Custom.1", each Table.AddIndexColumn([Data], "IndexNo", 1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Data"}),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "IndexNo"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "IndexNo"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom.1", each [Column12] <> null and [Column12] <> ""),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"IndexNo", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type", "MergedIndex", each Text.Combine({[Name],"-",[IndexNo]})),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom2",{{"Column1", "No"}, {"Column2", "Nama Paket"}, {"Column3", "No. Kontrak"}, {"Column4", "Nama Kontraktor"}, {"Column5", "Jenis Kontrak (Single Years/Multi Years)"}, {"Column6", "Tanggal Mulai Kontrak"}, {"Column7", "Tanggal Akhir Kontrak"}, {"Column8", "Nilai Kontrak (Rp)"}, {"Column9", "Nilai Setelah Addendum -Jika Ada (Rp)"}, {"Column10", "Nomor SP2D"}, {"Column11", "Tgl SP2D"}, {"Column12", "Nilai SPM per Paket"}, {"Column13", "Jenis Pembayaran"}, {"Column14", "Fisik Utama"}, {"Column15", "Bagian Fisik yang dicatat menjadi NUP KDP sendiri"}, {"Column16", "Supervisi"}, {"Column17", "Lainnya"}, {"Column18", "Jalan Dalam Pengerjaan (KDP)"}, {"Column19", "Jalan Nasional"}, {"Column20", "Transaksi Perolehan Lainnya (KDP)"}, {"Column21", "Dengan Transaksi Perolehan KDP (502)"}, {"Column22", "Dengan Transaksi Pengembangan KDP (503)"}, {"Column23", "Kode BMN"}, {"Column24", "NUP/No. KDP"}}),
#"Removed Duplicates" = Table.Distinct(#"Renamed Columns", {"MergedIndex"}),
#"Added Custom3" = Table.AddColumn(#"Removed Duplicates", "NumberOrText", each if Value.Is([Nilai SPM per Paket],type number)
then "Number"
else "Text"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Nilai SPM per Paket", type text}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type1", each not Text.Contains([Nilai SPM per Paket], " ") and not Text.Contains([Nilai SPM per Paket], "a") and not Text.Contains([Nilai SPM per Paket], "i") and not Text.Contains([Nilai SPM per Paket], "u") and not Text.Contains([Nilai SPM per Paket], "e") and not Text.Contains([Nilai SPM per Paket], "o") and not Text.Contains([Nilai SPM per Paket], "A") and not Text.Contains([Nilai SPM per Paket], "I") and not Text.Contains([Nilai SPM per Paket], "U") and not Text.Contains([Nilai SPM per Paket], "E") and not Text.Contains([Nilai SPM per Paket], "O") and [Nilai SPM per Paket] <> "12" and not Text.StartsWith([Nilai SPM per Paket], "…")),
#"Added Custom4" = Table.AddColumn(#"Filtered Rows2", "Nilai SPM per Paket (clean)", each if [NumberOrText] = "Number"
then [Nilai SPM per Paket]
else
if [NumberOrText] = "Text" and Text.Length([Nilai SPM per Paket]) <18
then let Result=Text.Replace(Text.Replace(Text.Replace([Nilai SPM per Paket],",",""),".",""),"-","")
in Result
else ""),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom4",{{"Nilai SPM per Paket (clean)", Int64.Type}}),
#"Added Custom5" = Table.AddColumn(#"Changed Type2", "Kode Satker", each Text.Middle([Name],0,6)),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom5",{"Name", "Date created", "Folder Path", "Kode Satker", "No", "Nama Paket", "No. Kontrak", "Nama Kontraktor", "Jenis Kontrak (Single Years/Multi Years)", "Tanggal Mulai Kontrak", "Tanggal Akhir Kontrak", "Nilai Kontrak (Rp)", "Nilai Setelah Addendum -Jika Ada (Rp)", "Nomor SP2D", "Tgl SP2D", "Nilai SPM per Paket", "Nilai SPM per Paket (clean)", "NumberOrText", "Jenis Pembayaran", "Fisik Utama", "Bagian Fisik yang dicatat menjadi NUP KDP sendiri", "Supervisi", "Lainnya", "Jalan Dalam Pengerjaan (KDP)", "Jalan Nasional", "Transaksi Perolehan Lainnya (KDP)", "Dengan Transaksi Perolehan KDP (502)", "Dengan Transaksi Pengembangan KDP (503)", "Kode BMN", "NUP/No. KDP", "Column25", "Column26", "Column27", "IndexNo", "MergedIndex"})
in
#"Reordered Columns"Sekian dulu ya, silahkan berdiskusi santun di kolom komentar 😉 Terima kasih.
Keren was here.
Latest posts by keren (see all)