Bills "Hvordan vil du rense disse dataene" -utfordringen - Excel-tips

Innholdsfortegnelse

Når jeg gjør et live Power Excel-seminar, tilbyr jeg at hvis noen i rommet noen gang har et merkelig Excel-problem, kan de sende det til meg for å få hjelp. Det var slik jeg fikk dette problemet med datarensing. Noen hadde et sammendragsark som ser slik ut:

Sammendrag regneark

De ønsket å omformatere dataene for å se slik ut:

Ønskede omformaterte data

En interessant ledetråd om disse dataene: 18 i G4 ser ut til å være en delsum av H4: K4. Det er fristende å fjerne kolonnene G, L og så videre, men først må du trekke ut ansattens navn fra G3, L3 og så videre.

Det var 04.00 søndag 9. februar da jeg skrudde på videoopptakeren og spilte inn noen klumpete trinn i Power Query for å løse problemet. Med tanke på at det var søndag, en dag jeg normalt ikke lager videoer, ba jeg folk om å sende inn sine ideer om hvordan de skulle løse problemet. Det er sendt inn 29 løsninger.

Hver løsning gir noen kule nye forbedringer i forhold til prosessen min. Planen min er å starte en serie artikler som viser de forskjellige forbedringene av metoden min.

Se på video

Før jeg starter prosessen, inviterer jeg deg til å se løsningen min:

Og M-koden som Power Query genererte for meg:

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 begynner å komme inn på løsningene, la oss ta opp mange vanlige kommentarer:

  • Noen av dere sa at du ville gå bakover for å finne ut hvorfor dataene vises i dette formatet til å begynne med. Jeg setter pris på disse kommentarene. Alle som sa dette er en bedre person enn meg. Jeg har lært gjennom årene at når du spør "Hvorfor?" svaret involverer vanligvis denne tidligere ansatte som startet på denne veien for 17 år siden, og alle fortsetter å bruke den på denne måten siden vi alle er vant til det nå.
  • Også - mange av dere - sa at den endelige løsningen skulle være en høy vertikal tabell og deretter bruke en pivottabell for å produsere de endelige resultatene. Jonathan Cooper oppsummerte dette best: "Jeg er også enig med noen av de andre YouTube-kommentarene om at et riktig datasett ikke ville ha" Totaler "og ikke ville trenge å bli dreid på slutten. Men hvis brukeren virkelig vil ha en vanlig gammelt bord så gir du dem det de vil ha. " Jeg kan faktisk se begge sider av dette. Jeg elsker et pivottabell, og det eneste morsommere enn Power Query er Power Query med et fint pivottabell på toppen. Men hvis vi kan gjøre det hele i Power Query, så en ting mindre å bryte.

Her er hyperkoblinger til forskjellige teknikker

  • Power Query-teknikker

    • Nummereringsgrupper av poster
    • Pakke ut to tegn til venstre
    • Total kolonne
    • Ellers hvis klausuler
    • Flere identiske overskrifter i Power Query
    • Hva du skal slette
    • Del av Q
    • Sortering av ordrelinjer
    • Power Query Solutions fra Excel MVPs

  • Beveger seg utenfor Power Query Interface

    • Tabell. Splitt
    • The World Of Bill Szysz

  • Formelløsninger

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

  • Sammensatt av alle ideer fra ovennevnte og endelige video

    • Sammensatt av de beste ideene fra alle

Interessante artikler...