Merk
Dette er en av en serie artikler som beskriver løsninger som er sendt inn for Podcast 2316-utfordringen.
I min originale video om omforming av data kom jeg til et punkt der jeg trengte å få de to første tegnene fra en kolonne. Min metode involverte Split Column for å generere de to første tegnene og alt annet. Jeg slettet da alt annet.
Mike Girvin fra ExcelisFun-kanalen, samt Brandon Brimberry, Wyn Hopkins fra Access Analytic, Geert Demulle bemerket at en raskere måte ville være å bruke Transform, Extract, First Characters.
Dette beholder bare kvartalsinformasjonen uten behov for å slette resten av kolonnen.
Bohdan Duda brukte Extracted Text Before Delimiter for å få alt før _-tegnet.
Mike Girvin bemerket at løsningen hans var "i farta, raskt før jeg er ute av døren." Her er koden hans:
let Source = Excel.CurrentWorkbook()((Name="BadStartData"))(Content), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, ("Category Description"), "Quarter", "Amount"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if not Text.StartsWith((Quarter),"Q") then (Quarter) else null), #"Filled Down" = Table.FillDown(#"Added Custom",("Employee")), #"Extracted First Characters" = Table.TransformColumns(#"Filled Down", (("Quarter", each Text.Start(_, 2), type text))), #"Filtered Rows" = Table.SelectRows(#"Extracted First Characters", each Text.StartsWith((Quarter), "Q")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Quarter)), "Quarter", "Amount", List.Sum), #"Filtered Rows1" = Table.SelectRows(#"Pivoted Column", each ((Employee) "Dept. Total")), #"Inserted Sum" = Table.AddColumn(#"Filtered Rows1", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee", Order.Ascending),("Category Description", Order.Ascending))), #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",(("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", type number), ("Total", type number), ("Employee", type text), ("Category Description", type text))) in #"Changed Type"Her er Geert DeMulles løsning:
let Source = Excel.CurrentWorkbook()((Name="tblUglyData"))(Content), #"Changed Type" = Table.TransformColumnTypes(Source,(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q12", type number), ("Q23", type number), ("Q34", Int64.Type), ("Q45", Int64.Type), ("Employee 2", Int64.Type), ("Q16", Int64.Type), ("Q27", Int64.Type), ("Q38", Int64.Type), ("Q49", Int64.Type), ("Employee 3", Int64.Type), ("Q110", Int64.Type), ("Q211", Int64.Type), ("Q312", Int64.Type), ("Q413", Int64.Type), ("Employee 4", type number), ("Q114", type number), ("Q215", type number), ("Q316", type number), ("Q417", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",("Dept. Total", "Q1", "Q2", "Q3", "Q4")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", ("Category Description"), "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if Text.StartsWith((Attribute), "Employee") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each Text.StartsWith((Attribute), "Q")), #"Extracted First Characters" = Table.TransformColumns(#"Filtered Rows", (("Attribute", each Text.Start(_, 2), type text))), #"Renamed Columns" = Table.RenameColumns(#"Extracted First Characters",(("Attribute", "Quarter"))), #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"(Quarter)), "Quarter", "Value"), #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",(("Employee", type text))), #"Inserted Sum" = Table.AddColumn(#"Changed Type1", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee", Order.Ascending), ("Category Description", Order.Ascending))) in #"Sorted Rows"Gå tilbake til hovedsiden for Podcast 2316-utfordringen.
Les neste artikkel i denne serien: Power Query: Adding a Total Column.
