Power Query: The World of Bill Szysz - Συμβουλές για το Excel

Αφού έλεγξα 29 διαφορετικές συμμετοχές, δηλώνω ότι ο νικητής είναι ο Bill Szysz! Ο Bill είναι ένας από αυτούς τους ανθρώπους που είναι άνετοι να πληκτρολογούν ερωτήματα χωρίς να χρησιμοποιούν τη διεπαφή Power Query. Γράφει, «Αυτό μπορεί να γίνει με δεκάδες τρόπους. Σας στέλνω τέσσερις. "

Θα πρέπει να κατεβάσετε τις λύσεις του Bill και να τις ακολουθήσετε ένα βήμα τη φορά χρησιμοποιώντας το παράθυρο Applied Steps.

Η ευκολότερη λύση του Bill είναι η μέθοδος Transpose. Οτιδήποτε με κόκκινο χρώμα είναι κωδικός που ο Μπιλ άλλαξε ή έγραψε με το χέρι:

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" 

Για τη μέθοδο Combine, ο Bill λέει ότι δείχνει τη γλώσσα Μ. Αλλά δεν θα το χρησιμοποιούσε σε επαγγελματική εφαρμογή. Ωστόσο, με μόνο τέσσερις γραμμές κώδικα M, είναι εντυπωσιακό. Οτιδήποτε με κόκκινο χρώμα γράφεται με το χέρι:

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 Helper Table γράφεται εν μέρει με το χέρι:

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 χρησιμοποιεί μια συνάρτηση fxUnpivot που έγραψε εδώ και πολύ καιρό. Κατεβάστε το βιβλίο εργασίας για να το δείτε.

Για να διαβάσετε το επόμενο άρθρο σε αυτήν τη σειρά: One Dynamic Array Formula.

Επιστρέψτε στην κύρια σελίδα για την πρόκληση Podcast 2316.