Bills udfordring "Hvordan vil du rense disse data" - Excel-tip

Indholdsfortegnelse

Når jeg laver et live Power Excel-seminar, tilbyder jeg, at hvis nogen i lokalet nogensinde har et ulige Excel-problem, kan de sende det til mig for at få hjælp. Det er sådan, jeg kom til at modtage dette datarensningsproblem. Nogen havde et oversigtsark, der ser sådan ud:

Resume regneark

De ønskede at omformatere dataene for at se sådan ud:

Ønskede omformaterede data

En interessant anelse om disse data: 18 i G4 ser ud til at være en subtotal af H4: K4. Det er fristende at fjerne kolonner G, L og så videre, men først skal du udtrække medarbejdernavnet fra G3, L3 osv.

Det var 4AM søndag den 9. februar, da jeg tændte videooptageren og optagede nogle klodsede trin i Power Query for at løse problemet. Da det var søndag, en dag, hvor jeg normalt ikke laver videoer, bad jeg folk om at sende deres ideer til, hvordan man løser problemet. Der er sendt 29 løsninger.

Hver løsning giver nogle seje nye forbedringer i forhold til min proces. Min plan er at starte en serie artikler, der viser de forskellige forbedringer af min metode.

Se video

Før jeg begynder denne proces, opfordrer jeg dig til at se min løsning:

Og den M-kode, som Power Query genererede for mig:

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))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "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))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",(("Attribute.1", "TextValue"))), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length((TextValue))), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if (Length)> 2 then (TextValue) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Name")), #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",("Category Description", "Name", "TextValue", "Value", "Length")), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ((TextValue) = "Q1" or (TextValue) = "Q2" or (TextValue) = "Q3" or (TextValue) = "Q4")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each (Name) "Dept. Total"), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Length")), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"(TextValue)), "TextValue", "Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Name", Order.Ascending))), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each (Q1)+(Q2)+(Q3)+(Q4)) in #"Added Custom1"

Før vi begynder at komme ind på løsningerne, lad os tage fat på mange almindelige kommentarer:

  • Nogle af jer sagde, at man ville gå baglæns for at finde ud af, hvorfor dataene vises i dette format til at begynde med. Jeg sætter pris på disse kommentarer. Alle, der sagde dette, er en bedre person end mig. Jeg har lært gennem årene, at når du spørger "Hvorfor?" svaret involverer normalt denne tidligere medarbejder, der startede denne vej for 17 år siden, og alle fortsætter med at bruge det på denne måde, da vi alle er vant til det nu.
  • Også - mange af jer - sagde, at den endelige løsning skulle være en høj lodret tabel og derefter bruge en drejetabel til at producere de endelige resultater. Jonathan Cooper opsummerede dette bedst: "Jeg er også enig med nogle af de andre YouTube-kommentarer om, at et ordentligt datasæt ikke ville have" Totaler "og ikke behøver at blive drejet i slutningen. Men hvis brugeren virkelig ønsker en almindelig gamle bord så giver du dem, hvad de vil have. " Jeg kan faktisk se begge sider af dette. Jeg elsker et drejebord, og det eneste, der er sjovere end Power Query, er Power Query med et dejligt pivottabel på toppen. Men hvis vi kan gøre det hele i Power Query, så en ting mindre at bryde.

Her er hyperlinks til forskellige teknikker

  • Power Query-teknikker

    • Nummereringsgrupper af poster
    • Uddrag af venstre to tegn
    • Samlet kolonne
    • Ellers hvis klausuler
    • Flere identiske overskrifter i strømforespørgsel
    • Hvad skal jeg slette?
    • Opdel med Q
    • Sortering af linjeposter
    • Power Query-løsninger fra Excel MVP'er

  • Bevæger sig ud over Power Query Interface

    • Tabel. Opdeling
    • Verden af ​​Bill Szysz

  • Formelløsninger

    • En dynamisk matrixformel
    • Old School Helper Columns
    • Formelløsninger

  • Sammensat af alle ideer fra ovenstående og endelige video

    • Sammensat af de bedste ideer fra alle

Interessante artikler...