Nakon pregleda 29 različitih prijava, proglašavam pobjednikom Bill Szysz! Bill je jedan od onih kojima je ugodno tipkati upite bez upotrebe sučelja Power Query. Piše: „To se može učiniti na desetke načina. Šaljem vam četvoricu. "
Trebali biste preuzeti Billova rješenja i prolaziti kroz njih korak po korak pomoću okna Primijenjeni koraci.
Billovo najlakše rješenje je metoda Transpose. Sve što je u crvenoj boji je kod koji je Bill promijenio ili napisao rukom:
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" Za kombiniranu metodu, Bill kaže da pokazuje jezik M. Ali to ne bi koristio u profesionalnoj aplikaciji. Međutim, sa samo četiri retka M koda, impresivan je. Sve što je crveno napisano je rukom:
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 Metoda Billovog pomagača napisana je djelomično rukom:
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"Billova posljednja metoda koristi funkciju fxUnpivot koju je davno napisao. Preuzmite radnu knjigu da biste je provjerili.
Da biste pročitali sljedeći članak iz ove serije: Formula jednog dinamičnog niza.
Povratak na glavnu stranicu za izazov Podcast 2316.
