Power Query: Bill Szyszi maailm - Exceli näpunäited

Pärast 29 erineva kande ülevaatamist kuulutan võitjaks Bill Szysz! Bill on üks neist inimestest, kellele on mugav päringuid kirjutada ilma Power Query liidest kasutamata. Ta kirjutab: „Seda saab teha mitmel viisil. Saadan teile neli. "

Peaksite Billi lahendused alla laadima ja paanil Rakendatud sammud läbi astuma.

Billi lihtsaim lahendus on meetod Transpose. Kõik punasega on kood, mille Bill muutis või kirjutas käsitsi:

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" 

Kombineerimismeetodi jaoks ütleb Bill, et see näitab M-keelt. Kuid ta ei kasutaks seda professionaalses rakenduses. Kuid ainult nelja M-koodi reaga on see muljetavaldav. Kõik punasega on kirjutatud käsitsi:

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 

Billi abistajate tabeli meetod on kirjutatud osaliselt käsitsi:

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"

Billi viimane meetod kasutab funktsiooni fxUnpivot, mille ta juba ammu kirjutas. Selle kontrollimiseks laadige töövihik alla.

Selle sarja järgmise artikli lugemiseks: Üks dünaamilise massiivi valem.

Naaske Podcast 2316 väljakutse avalehele.