La sfida "Come pulire questi dati" di Bill - Suggerimenti per Excel

Sommario

Quando faccio un seminario su Power Excel dal vivo, offro che se qualcuno nella stanza ha uno strano problema con Excel, può inviarmelo per chiedere aiuto. È così che sono arrivato a ricevere questo problema di pulizia dei dati. Qualcuno aveva un foglio di lavoro di riepilogo simile a questo:

Foglio di lavoro riepilogativo

Volevano riformattare i dati in questo modo:

Dati riformattati desiderati

Un indizio interessante su questi dati: il 18 in G4 sembra essere un totale parziale di H4: K4. Si è tentati di rimuovere le colonne G, L e così via, ma prima devi estrarre il nome del dipendente da G3, L3 e così via.

Erano le 4 del mattino di domenica 9 febbraio quando ho acceso il videoregistratore e ho registrato alcuni passaggi goffi in Power Query per risolvere il problema. Dato che era domenica, un giorno in cui normalmente non faccio video, ho chiesto alle persone di inviare le loro idee su come risolvere il problema. Sono state inviate 29 soluzioni.

Ogni soluzione offre alcuni nuovi interessanti miglioramenti al mio processo. Il mio piano è iniziare una serie di articoli che mostrano i vari miglioramenti al mio metodo.

Guarda un video

Prima di iniziare quel processo, ti invito a vedere la mia soluzione:

E il codice M generato da Power Query per me:

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"

Prima di iniziare ad entrare nelle soluzioni, affrontiamo molti commenti comuni:

  • Alcuni di voi hanno detto che sareste tornati indietro per capire perché i dati vengono visualizzati in questo formato per cominciare. Apprezzo questi commenti. Tutti quelli che hanno detto che questa è una persona migliore di me. Ho imparato negli anni che quando chiedi "Perché?" la risposta di solito coinvolge questo ex dipendente che ha intrapreso questo percorso 17 anni fa e tutti continuano a usarlo in questo modo poiché ora ci siamo abituati.
  • Inoltre, molti di voi hanno detto che la soluzione finale dovrebbe essere un tavolo verticale alto e quindi utilizzare una tabella pivot per produrre i risultati finali. Jonathan Cooper ha riassunto meglio questo punto: "Sono anche d'accordo con alcuni degli altri commenti di YouTube sul fatto che un set di dati corretto non avrebbe" Totali "e non avrebbe bisogno di essere ruotato alla fine. Ma se l'utente vuole davvero un semplice vecchio tavolo poi dai loro quello che vogliono. " Posso effettivamente vedere entrambi i lati di questo. Adoro una tabella pivot e l'unica cosa più divertente di Power Query è Power Query con una bella tabella pivot in cima. Ma se possiamo fare il tutto in Power Query, allora una cosa in meno da rompere.

Ecco i collegamenti ipertestuali a varie tecniche

  • Tecniche di Power Query

    • Numerazione di gruppi di record
    • Estrazione di due caratteri a sinistra
    • Colonna totale
    • Else if clausole
    • Più intestazioni identiche in Power Query
    • Cosa eliminare
    • Dividi per Q
    • Ordinamento degli elementi pubblicitari
    • Soluzioni Power Query da MVP di Excel

  • Andare oltre l'interfaccia di Power Query

    • Table.Split
    • Il mondo di Bill Szysz

  • Soluzioni in formula

    • Una formula di array dinamico
    • Colonne di aiuto della vecchia scuola
    • Soluzioni in formula

  • Composito di tutte le idee dall'alto e dal video finale

    • Composito delle migliori idee da tutti

Articoli interessanti...