Bemærk
Dette er en af en række artikler, der beskriver løsninger sendt til Podcast 2316-udfordringen.
I min originale video om omformning af data ankom jeg til et punkt, hvor jeg havde brug for at få de første 2 tegn fra en kolonne. Min metode involverede Split kolonne til at generere de første 2 tegn og alt andet. Jeg slettede derefter alt det andet.
Mike Girvin fra ExcelisFun-kanalen samt Brandon Brimberry, Wyn Hopkins fra Access Analytic, Geert Demulle bemærkede, at en hurtigere måde ville være at bruge Transform, Extract, First Characters.
Dette gemmer kun kvartalsoplysningerne uden behov for at slette resten af kolonnen.
Bohdan Duda brugte uddraget tekst før afgrænsning for at få alt før _-tegnet.
Mike Girvin bemærkede, at hans løsning var "i farten, hurtigt inden jeg er ude af døren." Her er hans kode:
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 DeMulle's 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å tilbage til hovedsiden for Podcast 2316-udfordringen.
Læs den næste artikel i denne serie: Power Query: Tilføjelse af en samlet kolonne.
