Catatan
Ini adalah salah satu rangkaian artikel yang memperincikan penyelesaian yang dihantar untuk cabaran Podcast 2316.
Dalam penyelesaian saya untuk membentuk semula data, saya mahukan cara untuk melihat apakah lajur mengandungi nama pekerja atau nilai seperti Q1, Q2, Q3, Q4. Dalam penyelesaian saya, saya mengandaikan bahawa tidak ada yang akan mempunyai nama dengan 2 watak, dan oleh itu saya menambahkan lajur untuk mengira panjang teks dalam lajur.
Jason M mengelakkan keperluan untuk lajur Panjang dengan menambahkan tiga klausa Else If pada Lajur Bersyaratnya.
Pengiraan bersyarat untuk Pekerja kemudian mencari Kuartal menjadi Null: if (Quarter) = null maka (Category Description) lain null.
Inilah kod M Jason:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Transposed Table" = Table.Transpose(Source), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Added Conditional Column" = Table.AddColumn(#"Promoted Headers", "Quarter", each if (Category Description) = "Q1" then (Category Description) else if (Category Description) = "Q2" then (Category Description) else if (Category Description) = "Q3" then (Category Description) else if (Category Description) = "Q4" then (Category Description) else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Employee", each if (Quarter) = null then (Category Description) else null), #"Filled Up" = Table.FillUp(#"Added Conditional Column1",("Quarter")), #"Filled Down" = Table.FillDown(#"Filled Up",("Employee")), #"Inserted Distinct Count" = Table.AddColumn(#"Filled Down", "Distinct Count", each List.NonNullCount(List.Distinct(((Category Description), (Employee)))), Int64.Type), #"Filtered Rows1" = Table.SelectRows(#"Inserted Distinct Count", each (Distinct Count) 1), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Distinct Count")), #"Removed Columns" = Table.RemoveColumns(#"Removed Columns1",("Category Description")), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ((Employee) "Dept. Total")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", ("Quarter", "Employee"), "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",(("Attribute", "Category Description"))), #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"(Quarter)), "Quarter", "Value"), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Reordered Columns" = Table.ReorderColumns(#"Inserted Sum",("Category Description", "Employee", "Q1", "Q2", "Q3", "Q4", "Total")) in #"Reordered Columns"Ondřej Malinský menghantar penyelesaian yang menggunakan banyak klausa Else If juga:
Matthew Wykle menghantar penyelesaian dengan cara lain untuk mengenal pasti tempatnya. Kaedahnya memeriksa bahawa teks bermula dengan Q dan digit kedua kurang dari 5:
if Text.Start((Attribute),1)="Q" and Number.From(Text.Middle((Attribute),1,1))<5 then Text.Start((Attribute),2) else "Total")
Christian Neuberger menggunakan formula ini untuk mendapatkan Nama Pekerja, Diisi, dan kemudian ditapis lajur 1 untuk menyertakan hanya Q1, Q2, Q3, atau Q4. Oz Du Soleil juga menggunakan kaedah ini.
Excel MVP Ken Puls mungkin menang dengan rumusnya. Ia mencari garis bawah untuk mengetahui apakah ini bukan nama pekerja.
Lihat penyelesaian lengkap Ken di Excel MVPs Attack the Data Cleansing Problem in Power Query.
Kembali ke halaman utama untuk cabaran Podcast 2316.
Baca artikel seterusnya dalam siri ini: Kuasa Kuasa: Berurusan dengan Pelbagai Tajuk yang Sama.
