Billi väljakutse "Kuidas te neid andmeid puhastaksite" - Exceli näpunäited

Lang L: none (table-of-contents)

Kui teen otseülekannet Power Exceli seminarile, pakun, et kui kellelgi ruumis viibivatest inimestest on kunagi mõni veider Exceli probleem, võib ta selle mulle abi saamiseks saata. Nii jõudsin selle andmete puhastamise probleemini. Kellelgi oli kokkuvõtlik tööleht, mis näeb välja selline:

Kokkuvõtlik tööleht

Nad tahtsid andmed ümber vormindada järgmiselt:

Soovitud vormindatud andmed

Üks huvitav vihje nende andmete kohta: 18 G4-s näib olevat H4: K4 vahesumma. Veergude G, L ja muu eemaldamine on ahvatlev, kuid kõigepealt peate töötaja nime välja võtma G3, L3 jne.

Pühapäeval, 9. veebruaril oli kell 4 hommikul, kui lülitasin videosalvesti sisse ja salvestasin Power Query'sse mõned kohmakad sammud probleemi lahendamiseks. Arvestades, et oli pühapäev, päev, kus ma tavaliselt videoid ei tee, palusin inimestel saata oma ideed, kuidas probleemi lahendada. 29 lahendust on saadetud.

Iga lahendus pakub minu protsessile uut lahedat täiustust. Minu plaan on alustada artiklite sarja, mis näitab minu meetodi erinevaid täiustusi.

Vaata videot

Enne selle protsessi alustamist kutsun teid tutvuma minu lahendusega:

Ja M-kood, mille Power Query minu jaoks genereeris:

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"

Enne lahenduste leidmist käsitleme paljusid levinud kommentaare:

  • Mõni teist ütles, et lähete tagasi, et aru saada, miks andmeid selles vormingus kõigepealt kuvatakse. Ma hindan neid kommentaare. Kõik, kes seda ütlesid, on parem inimene kui mina. Olen aastate jooksul õppinud, et kui küsida "Miks?" vastus hõlmab tavaliselt seda endist töötajat, kes alustas seda teed 17 aastat tagasi, ja kõik kasutavad seda jätkuvalt, kuna me kõik oleme nüüd harjunud.
  • Samuti - paljud teist - ütles, et lõplik lahendus peaks olema kõrge vertikaalne laud ja seejärel kasutage lõplike tulemuste saamiseks pöördtabelit. Jonathan Cooper võttis selle kõige paremini kokku: "Nõustun ka mõne teise YouTube'i kommentaariga, et korralikul andmekogumil ei oleks" Kokku "ja seda poleks vaja lõpuks pöörata. Kuid kui kasutaja tõesti soovib lihtsat vana laud siis annad neile, mida nad tahavad. " Ma näen tegelikult selle mõlemat külge. Ma armastan pöördlauda ja ainus asi, mis on lõbusam kui Power Query, on Power Query, mille peal on kena pöördlaud. Aga kui me saame Power Queryga kogu asja ära teha, siis rikkuda tuleb veel üks asi.

Siin on hüperlingid erinevatele tehnikatele

  • Power Query tehnikad

    • Dokumentide rühmade nummerdamine
    • Kahe vasakpoolse märgi ekstraheerimine
    • Veerg kokku
    • Muul juhul, kui klauslid
    • Mitu ühesugust päist Power Queryis
    • Mida kustutada
    • Jagatud Q järgi
    • Ridaüksuste sortimine
    • Power Query lahendused Exceli MVP-delt

  • Liikumine kaugemale Power Query liidesest

    • Tabel. Jagatud
    • Bill Szyszi maailm

  • Vormelahendused

    • Üks dünaamilise massiivi valem
    • Vana kooli abikolonnid
    • Vormelahendused

  • Kõigi ülaltoodud ideede ja lõpliku video liit

    • Kõigi parimate ideede liit

Huvitavad Artiklid...