Gli MVP di Excel affrontano il problema della pulizia dei dati in Power Query - Suggerimenti per Excel

Sommario

Nota

Questo è uno di una serie di articoli che descrivono in dettaglio le soluzioni inviate per la sfida Podcast 2316.

L'MVP di Excel Oz Du Soleil del canale Excel on Fire su YouTube ha menzionato il Bull Rider brasiliano Kaique Pachecho. Oz è stata la prima persona a notare che sono andato lentamente per aggiungere i quattro quarti.

Il video di Oz è:
https://www.youtube.com/watch?v=OluZlF44PNI

Il suo codice è:

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"

Un'altra soluzione, questa da Excel MVP John MacDougall.

  • John è stato il primo a dire che eliminando i due passaggi aggiuntivi aggiunti da Power Query, si eliminano i suffissi dispari nelle intestazioni duplicate Q1 Q2 Q3 Q4.
  • John ha utilizzato all'inizio una colonna Indice che sarebbe stata utilizzata alla fine per l'ordinamento. Ma - John ha concatenato la sua colonna indice dopo la descrizione della categoria. Ha usato un carattere pipe verticale | in modo da poter rompere i dati più tardi.
  • John ha digitato la sua colonna condizionale come colonna personalizzata invece di utilizzare l'interfaccia Colonna condizionale.
Colonna condizionale come colonna personalizzata

Guarda il video di John qui:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

L'MVP di Excel Ken Puls, coautore del libro M is for (Data) Monkey, inviato in tre soluzioni. La sua colonna condizionale è probabilmente la più breve.

Ma la soluzione preferita di Ken ignora la domanda originale. Invece di creare la tabella in Power Query, crea un set di dati pivot in Power Query e quindi termina con una tabella pivot.

L'anteprima finale di Ken in Power Query è simile a questa:

Set di dati pivotable

Ecco il codice di Ken:

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"

Dopo aver creato questa query solo come connessione, utilizza una tabella pivot per creare il rapporto finale.

Rapporto finale con tabella pivot

Soluzioni di altri MVP:

  • Il codice Wyn Hopkins si trova qui: Power Query: trattare con più intestazioni identiche.
  • Il codice di Mike Girvin è qui: Power Query: estrazione di 2 caratteri a sinistra da una colonna.
  • La soluzione della formula di Roger Govier è qui: Formula Solutions.

Torna alla pagina principale per la sfida Podcast 2316.

Leggi il prossimo articolo di questa serie: Power Query: Beyond the User Interface: Table.Split e altro.

Articoli interessanti...