Notă
Acesta este unul dintr-o serie de articole care detaliază soluțiile trimise pentru provocarea Podcast 2316.
Excel MVP Oz Du Soleil de la canalul Excel on Fire de pe YouTube a menționat brazilianul Bull Rider Kaique Pachecho. Oz a fost prima persoană care a observat că am mers pe calea lentă pentru a adăuga cele patru sferturi.
Videoclipul lui Oz este:
https://www.youtube.com/watch?v=OluZlF44PNI
Codul său este:
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"O altă soluție, aceasta de la Excel MVP John MacDougall.
- John a fost primul care a spus că, prin ștergerea celor doi pași suplimentari Power Query adăugați, eliminați sufixele ciudate de pe titlurile duplicate Q1 Q2 Q3 Q4.
- John a folosit timpuriu o coloană Index care ar fi folosită la sfârșit pentru sortare. Dar - John a concatenat coloana de index după descrierea categoriei. A folosit un caracter vertical de pipă | astfel încât să poată divulga datele mai târziu.
- John și-a tastat coloana condițională ca o coloană personalizată în loc să folosească interfața coloanei condiționate.
Urmăriți videoclipul lui John aici:
https://www.youtube.com/watch?v=Dqmb6SEJDXI
MVP Excel Ken Puls, co-autor al cărții M este pentru (Data) Maimuță trimisă în trei soluții. Coloana sa condiționată este probabil cea mai scurtă.
Dar soluția preferată a lui Ken ignoră întrebarea inițială. În loc să creeze tabelul în Power Query, el creează un set de date pivotabil în Power Query și apoi termină cu un tabel pivot.
Previzualizarea finală a lui Ken în Power Query arată astfel:
Iată codul lui 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"După ce a creat această interogare doar ca conexiune, el folosește apoi un tabel pivot pentru a crea raportul final.
Soluții de la alte MVP-uri:
- Codul Wyn Hopkins este aici: Power Query: Gestionarea mai multor anteturi identice.
- Codul lui Mike Girvin este aici: Power Interogare: Extragerea celor 2 caractere din stânga dintr-o coloană.
- Soluția de formulă a lui Roger Govier este aici: Formula Solutions.
Reveniți la pagina principală pentru provocarea Podcast 2316.
Citiți următorul articol din această serie: Power Query: Beyond the User Interface: Table.Split and More.
