Nota
Questo è uno di una serie di articoli che descrivono in dettaglio le soluzioni inviate per la sfida Podcast 2316.
Nel mio problema originale di modellazione dei dati, mi sono imbattuto in un problema molto presto nel processo. I dati in arrivo avrebbero avuto molte colonne con un'intestazione Q1.
Nella mia soluzione, ho creato un intervallo denominato "UglyData" e l'ho importato in Power Query. Ciò ha portato al risultato infelice di Power Query che ha rinominato le mie colonne in Q1_1.
Successivamente, dopo aver annullato il pivot, ho dovuto estrarre solo i due caratteri di sinistra da quelle intestazioni.
C'erano tre soluzioni separate a questo problema:
- Wyn Hopkins e Demote Headers
- MF Wong e deseleziona My Table Has Headers (suggerito anche da Peter Bartholomew)
- Jason M ed elimina semplicemente le intestazioni promosse (suggerito anche da Ondřej Malinský e dall'MVP di Excel John MacDougall)
La prima innovazione è stata di Wyn Hopkins di Access Analytic. Invece di un intervallo denominato, Wyn ha convertito i dati in una tabella utilizzando Ctrl + T. A questo punto, il danno alle intestazioni è stato fatto, poiché Excel ha convertito le intestazioni in:
Una volta che Wyn ha inserito i dati in Power Query, ha quindi aperto il menu a discesa Usa prima riga come intestazioni e ha scelto Usa intestazioni come prima riga. Non ho mai capito che fosse lì. Crea un passaggio chiamato Table.DemoteHeaders.
Ma, anche con il miglioramento di Wyn, in seguito avrebbe comunque dovuto estrarre i primi 2 caratteri da quelle intestazioni.
La seconda innovazione è la tecnica di MF Wong. Quando ha creato la tabella, ha deselezionato La mia tabella ha intestazioni!
Ciò garantisce che Excel lasci le intestazioni Q1 multiple da sole e non sia necessario estrarre il suffisso aggiuntivo in un secondo momento.
Capisco che ci siano persone nel campo "I love tables". Il video di MF Wong ha dimostrato come potrebbe aggiungere nuovi dipendenti a destra dei dati e la tabella si espande automaticamente. Ci sono molti buoni motivi per usare le tabelle.
Tuttavia, poiché adoro i totali parziali, le visualizzazioni personalizzate e il filtro per selezione, tendo a non utilizzare le tabelle. Quindi, apprezzo la soluzione di Jason M. Ha mantenuto i dati come intervallo denominato di UglyData. Non appena ha importato i dati in Power Query, ha eliminato questi due passaggi:
Ora, con i dati semplicemente nella riga 1, non c'è problema con molte colonne chiamate Q1.
Ecco il codice di Wyn Hopkin che mostra DemotedHeaders:
let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Demoted Headers1" = Table.DemoteHeaders(Source), #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"), #"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each if Text.Start((Column1),1) = "Q" then null else (Column1)), #"Filled Down" = Table.FillDown(#"Added Custom",("Custom")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Custom) "Dept. Total")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith((Column1), "Employee")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", (PromoteAllScalars=true)), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", (("Category Description", each Text.Start(_, 2), type text))), #"Reordered Columns" = Table.ReorderColumns(#"Extracted First Characters",("Category Description_1", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category Description_1", "Category Description"), "Attribute", "Value"), #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",("Category Description_1", "Attribute", "Category Description", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"(#"Category Description")), "Category Description", "Value", List.Sum), #"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",("Attribute", "Category Description_1", "Q1", "Q2", "Q3", "Q4")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",(("Attribute", "Cat Deasc"), ("Category Description_1", "Emp Name"))), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",(("Emp Name", type text), ("Q1", Int64.Type), ("Q2", Int64.Type), ("Q3", Int64.Type), ("Q4", Int64.Type))), #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), Int64.Type) in #"Inserted Sum"Torna alla pagina principale per la sfida Podcast 2316.
Leggi il prossimo articolo di questa serie: Power Query: eliminare questo, eliminare quelli o eliminare nulla ?.
