Notă
Acesta este unul dintr-o serie de articole care detaliază soluțiile trimise pentru provocarea Podcast 2316.
În problema mea originală de formare a datelor, am întâmpinat o problemă foarte devreme în proces. Datele primite aveau să aibă multe coloane cu titlul Q1.
În soluția mea, am creat o gamă numită „UglyData” și am importat-o în Power Query. Acest lucru a dus la rezultatul nefericit al Power Query redenumirea coloanelor mele la Q1_1.
Mai târziu, după nepivotare, a trebuit să extrag doar cele două personaje din stânga din aceste anteturi.
Există trei soluții separate pentru această problemă:
- Wyn Hopkins și anteturile Demote
- MF Wong și debifează My Table Has Headers (sugerat și de Peter Bartholomew)
- Jason M și pur și simplu ștergeți anteturile promovate (sugerat și de Ondřej Malinský și MVP Excel John MacDougall)
Prima inovație a fost de la Wyn Hopkins la Access Analytic. În loc de un interval numit, Wyn a convertit datele într-un tabel folosind Ctrl + T. În acest moment, s-au făcut pagube la titluri, deoarece Excel a convertit titlurile la:
Odată ce Wyn a preluat datele în Power Query, a deschis apoi meniul drop-down Utilizați primul rând ca anteturi și a ales Utilizați antetele ca primul rând. Nu mi-am dat seama niciodată că asta era acolo. Se creează un pas numit Table.DemoteHeaders.
Dar, chiar și cu îmbunătățirea lui Wyn, el va trebui să extragă mai târziu primele 2 caractere din acele anteturi.
A doua inovație este tehnica lui MF Wong. Când a creat tabelul, a debifat Masa mea are anteturi!
Acest lucru asigură faptul că Excel lasă singurele antete Q1 multiple și nu este nevoie să extrageți sufixul suplimentar ulterior.
Înțeleg că sunt oameni în tabăra „Iubesc mesele”. Videoclipul lui MF Wong a demonstrat cum ar putea adăuga noi angajați la dreapta datelor și tabelul se extinde automat. Există o mulțime de motive întemeiate pentru a utiliza tabele.
Dar, pentru că ador subtotalurile, vizualizările personalizate și filtrul după selecție, tind să nu folosesc tabele. Deci, apreciez soluția de la Jason M. El a păstrat datele ca gama numită UglyData. De îndată ce a importat datele în Power Query, a șters acești doi pași:
Acum, cu datele pur și simplu în rândul 1, nu există probleme cu multe coloane numite Q1.
Iată codul lui Wyn Hopkin care arată 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"Reveniți la pagina principală pentru provocarea Podcast 2316.
Citiți următorul articol din această serie: Power Query: Ștergeți acest lucru, Ștergeți-le sau nu ștergeți nimic ?.
