29 farklı girişi inceledikten sonra, kazananı Bill Szysz olarak ilan ediyorum! Bill, Power Query arayüzünü kullanmadan rahatça sorgu yazabilen insanlardan biridir. Şöyle yazıyor: “Bu onlarca yolla yapılabilir. Sana dört tane gönderiyorum. "
Bill'in çözümlerini indirmeli ve Uygulanan Adımlar bölmesini kullanarak her seferinde bir adım adım ilerlemelisiniz.
Bill'in en kolay çözümü Transpoze yöntemidir. Kırmızı olan herhangi bir şey, Bill'in el ile değiştirdiği veya yazdığı koddur:
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" Birleştirme Yöntemi için Bill, M dilini gösterdiğini söylüyor. Ancak bunu profesyonel bir uygulamada kullanmaz. Bununla birlikte, yalnızca dört satırlık M koduyla etkileyici. Kırmızı olan her şey elle yazılır:
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 Bill'in Yardımcı Tablo yöntemi kısmen elle yazılmıştır:
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"Bill'in son yöntemi, uzun zaman önce yazdığı bir fxUnpivot işlevini kullanıyor. Kontrol etmek için çalışma kitabını indirin.
Bu dizideki sonraki makaleyi okumak için: Bir Dinamik Dizi Formülü.
Podcast 2316 yarışması için ana sayfaya dönün.
