Nota
Questo è uno di una serie di articoli che descrivono in dettaglio le soluzioni inviate per la sfida Podcast 2316.
Uno dei problemi con la mia soluzione è che la sequenza finale delle categorie non corrispondeva necessariamente alla sequenza originale delle colonne. Me ne sono reso conto alla fine del mio video e, poiché non era particolarmente importante, non me ne sono preoccupato.
Tuttavia, Josh Johnson ha inviato una soluzione che l'ha gestita. Quando Josh ha detto di aver usato una colonna Indice, ho pensato che fosse come Indice e Modulo in Power Query: Numero gruppi di record da 1 a 5 ripetutamente. Ma l'uso di Josh era completamente diverso.
Nota: anche l'MVP di Excel John MacDougall ha utilizzato questo metodo, ma ha concatenato la colonna dell'indice alla fine della descrizione della categoria. Guarda il video di John qui: https://www.youtube.com/watch?v=Dqmb6SEJDXI e leggi di più sul suo codice qui: Excel MVPs Attack the Data Cleansing Problem in Power Query.
All'inizio del processo, quando Josh aveva ancora solo sei record, ha aggiunto un indice a partire da 1. Josh ha fatto clic nella barra della formula e ha rinominato la colonna Indice come Categoria.
La colonna Categoria era la nuova ultima colonna. Ha usato Move, to Beginning per spostarlo per essere il primo:
Dopo questo, avvengono molti altri passaggi. Sono passaggi innovativi, ma finora sono stati trattati principalmente negli altri articoli. Dopo molti di questi passaggi, stavo iniziando a pensare che i numeri di categoria da 1 a 6 fossero solo un errore. Ho pensato che forse Josh li avrebbe cancellati senza usarli.
Josh Unpivots, quindi colonna condizionale, quindi riempire, quindi pivot, aggiunge il totale. Sembra che non utilizzi mai quella colonna Categoria. Dopo tanti passaggi, è qui:
Ma poi nei passaggi finali, Josh ordina i dati in base al nome del dipendente, quindi alla categoria!
A questo punto, può eliminare la colonna Categoria. L'ultima differenza: la PTO viene prima del Progetto A, proprio come nelle colonne originali. È un bel tocco.
Sottolineerò anche che Josh ha inviato un video di lui mentre esegue questi passaggi. Complimenti a Josh per aver utilizzato le scorciatoie da tastiera all'interno di Power Query!
Ecco il codice di Josh:
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))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"Torna alla pagina principale per la sfida Podcast 2316.
Leggi il prossimo articolo di questa serie: Gli MVP di Excel attaccano il problema della pulizia dei dati in Power Query.
