Setelah menyemak 29 entri berbeza, saya menyatakan pemenangnya sebagai Bill Szysz! Bill adalah salah satu daripada mereka yang selesa menaip pertanyaan tanpa menggunakan antara muka Power Query. Dia menulis, "Ini dapat dilakukan dengan puluhan cara. Saya menghantar anda berempat. "
Anda harus memuat turun penyelesaian Bill dan melaluinya selangkah demi selangkah menggunakan panel Langkah Terapan.
Penyelesaian termudah Bill adalah kaedah Transpose. Apa sahaja yang berwarna merah adalah kod yang diubah atau ditulis oleh Bill dengan tangan:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Transposed Table" = Table.Transpose(Source), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Indeks", 0, 1), #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", (("Indeks", each Number.IntegerDivide(_, 5), Int64.Type))), #"Grouped Rows" = Table.Group(#"Integer-Divided Column", ("Indeks"), (("tbl", each Table.Skip(_, 1), type table), ("Name", each _(0)(Category Description), type text))), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ((Name) "Dept. Total")), #"Expanded (0)" = Table.ExpandTableColumn(#"Filtered Rows", "tbl", Table.ColumnNames(#"Promoted Headers")), #"Removed Columns" = Table.RemoveColumns(#"Expanded (0)",("Indeks")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", ("Category Description", "Name"), "Atrybut", "Wartość"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(#"Category Description")), "Category Description", "Wartość", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns" = Table.RenameColumns(#"Inserted Sum",(("Atrybut", "Category Description"))) in #"Renamed Columns" Untuk Kaedah Gabungan, Bill mengatakan ia menunjukkan bahasa M. Tetapi dia tidak akan menggunakan ini dalam aplikasi profesional. Namun, dengan hanya empat baris kod M, ia sangat mengagumkan. Apa-apa yang berwarna merah ditulis dengan tangan:
let Source = Table.PromoteHeaders(Excel.CurrentWorkbook()((Name="UglyData"))(Content) , (PromoteAllScalars=true)), Lists = Table.FromColumns((List.Transform((0… ((List.Count(Table.ColumnNames(Source))-6)/5)-1), each List.Range(Table.ColumnNames(Source), _*5+6, 5)) )), AlmostReady = Table.Combine(Table.AddColumn(Lists, "Columns", each Table.FromColumns((Table.Column(Source, Table.ColumnNames(Source)(0))) & (List.Repeat(((Column1)(0)), Table.RowCount(Source))) & List.Transform(List.Skip((Column1), 1), each Table.Column(Source, _)), (Table.ColumnNames(Source)(0), "Name") & List.Transform(List.Skip((Column1), 1), each Text.BeforeDelimiter(_, "_")) ) )(Columns)), Ready = Table.AddColumn(AlmostReady, "Total", each List.Sum(List.Skip(Record.ToList(_), 2))) in Ready Kaedah Meja Penolong Bill ditulis sebahagiannya dengan tangan:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), HelperTbl = Table.FromColumns((List.Skip(Table.ColumnNames(#"Promoted Headers"), 1), List.Transform((0… List.Count(Table.ColumnNames(#"Promoted Headers"))-2), each Number.IntegerDivide(_, 5)))), #"Grouped Rows" = Table.Group(HelperTbl, ("Column2"), (("tbl", each Table.TransformColumnNames(Table.SelectColumns(#"Promoted Headers", _(Column1) & (Table.ColumnNames(#"Promoted Headers")(0))), each Text.BeforeDelimiter(_, "_")), type table))), Combined = Table.Combine(Table.AddColumn(#"Grouped Rows", "Tables", (x) => Table.SelectColumns(Table.AddColumn(x(tbl), "Name", each Table.ColumnNames(x(tbl))(0)), List.Skip(Table.ColumnNames(x(tbl)), 1) & ("Name")) ) (Tables)), #"Filtered Rows" = Table.SelectRows(Combined, each ((Name) "Dept. Total")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",List.LastN(Table.ColumnNames(#"Filtered Rows"), 2) & List.RemoveLastN(Table.ColumnNames(#"Filtered Rows"), 2)) in #"Removed Other Columns"Kaedah terakhir Bill menggunakan fungsi fxUnpivot yang ditulisnya sejak dulu. Muat turun buku kerja untuk melihatnya.
Untuk membaca artikel seterusnya dalam siri ini: One Dynamic Array Formula.
Kembali ke halaman utama untuk cabaran Podcast 2316.
