Excel MVP'er angriber datarensningsproblemet i Power Query - Excel-tip

Indholdsfortegnelse

Bemærk

Dette er en af en række artikler, der beskriver løsninger sendt til Podcast 2316-udfordringen.

Excel MVP Oz Du Soleil fra Excel on Fire-kanalen på YouTube nævnte den brasilianske Bull Rider Kaique Pachecho. Oz var den første person, der bemærkede, at jeg gik langsomt for at tilføje de fire kvartaler.

Ozs video er:
https://www.youtube.com/watch?v=OluZlF44PNI

Hans kode er:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Removed Columns" = Table.RemoveColumns(Source,("Column2", "Column3", "Column4", "Column5", "Column6")), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Administrative", type number), ("Holiday", Int64.Type), ("PTO/LOA/Jury Duty", Int64.Type), ("Project A", type number), ("Project B", type number), ("Project C", type number))), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if (Category Description) = "Q1" then null else if (Category Description) = "Q2" then null else if (Category Description) = "Q3" then null else if (Category Description) = "Q4" then null else (Category Description)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Custom")), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",(("Custom", "Names"))), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each (Category Description) = "Q1" or (Category Description) = "Q2" or (Category Description) = "Q3" or (Category Description) = "Q4"), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",("Names", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Names", "Category Description"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(#"Category Description")), "Category Description", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Sum",(("Addition", "TOTAL"))) in #"Renamed Columns1"

En anden løsning, denne fra Excel MVP John MacDougall.

John var den første til at sige, at ved at slette de to ekstra trin, som Power Query tilføjede, fjerner du de ulige suffikser på duplikatet Q1 Q2 Q3 Q4 overskrifter.
John brugte en indeks-kolonne tidligt, som ville blive brugt i slutningen til sortering. Men - John sammenkædede sin indeks kolonne efter kategoribeskrivelsen. Han brugte en lodret rørkarakter så han kunne bryde dataene ud senere.
John skrev sin betingede kolonne som en brugerdefineret kolonne i stedet for at bruge grænsefladen Betinget kolonne.

Betinget kolonne som en brugerdefineret kolonne

Se Johns video her:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

Excel MVP Ken Puls, medforfatter til M er til (Data) Monkey book sendt i tre løsninger. Hans betingede søjle er sandsynligvis den korteste.

Men Ken's foretrukne løsning ignorerer det oprindelige spørgsmål. I stedet for at oprette tabellen i Power Query opretter han et drejeligt datasæt i Power Query og slutter derefter med en pivottabel.

Kens sidste forhåndsvisning i Power Query ser sådan ud:

Drejeligt datasæt

Her er Ken's kode:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q1_1", type number), ("Q2_2", type number), ("Q3_3", Int64.Type), ("Q4_4", Int64.Type), ("Employee 2", Int64.Type), ("Q1_5", Int64.Type), ("Q2_6", Int64.Type), ("Q3_7", Int64.Type), ("Q4_8", Int64.Type), ("Employee 3", Int64.Type), ("Q1_9", Int64.Type), ("Q2_10", Int64.Type), ("Q3_11", Int64.Type), ("Q4_12", Int64.Type), ("Employee 4", type number), ("Q1_13", type number), ("Q2_14", type number), ("Q3_15", type number), ("Q4_16", 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.Contains((Attribute), "_") then null else (Attribute)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ((Attribute.2) null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",(("Attribute.1", "Quarter"), ("Value", "Amount"))), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",(("Category Description", type text), ("Quarter", type text), ("Amount", type number), ("Employee", type text))) in #"Changed Type2"

Efter at have oprettet denne forespørgsel kun som en forbindelse, bruger han derefter en pivottabel til at oprette den endelige rapport.

Afsluttende rapport med pivottabel

Løsninger fra andre MVP'er:

Wyn Hopkins-koden er her: Power Query: Håndterer flere identiske headere.
Mike Girvins kode er her: Power Query: Udpakning af venstre 2 tegn fra en kolonne.
Roger Goviers formelopløsning er her: Formelløsninger.

Gå tilbage til hovedsiden for Podcast 2316-udfordringen.

Læs den næste artikel i denne serie: Power Query: Beyond the User Interface: Table.Split and More.