Huomautus
Tämä on yksi artikkelisarjasta, joka sisältää yksityiskohtaiset ratkaisut, jotka lähetettiin Podcast 2316 -haasteeseen.
Excel on Fire -kanavan YouTube MVP Oz Du Soleil mainitsi YouTuben brasilialaisen ratsastaja Kaique Pachechon. Oz oli ensimmäinen henkilö, joka huomasi, että menin hitaasti lisäämällä neljä neljäsosaa.
Ozin video on:
https://www.youtube.com/watch?v=OluZlF44PNI
Hänen koodinsa on:
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"Toinen ratkaisu, tämä Excel MVP: n John MacDougallilta.
- John sanoi ensimmäisenä, että poistamalla Power Query -toiminnon lisäämät kaksi ylimääräistä vaihetta, poistat parittomat loppuliitteet päällekkäisissä Q1 Q2 Q3 Q4 -otsikoissa.
- John käytti aikaisin Index-saraketta, jota käytettiin lopussa lajittelussa. Mutta - John ketjutti hakemistosarakkeensa luokan kuvauksen jälkeen. Hän käytti pystysuoraa putkihahmoa | jotta hän voisi jakaa tiedot myöhemmin.
- John kirjoitti ehdollisen sarakkeen mukautetuksi sarakkeeksi ehdollisen sarakkeen käyttöliittymän sijaan.
Katso Johnin video täältä:
https://www.youtube.com/watch?v=Dqmb6SEJDXI
Excel MVP Ken Puls, M: n kirjoittaja, on (Data) Apina-kirja, joka lähetetään kolmella ratkaisulla. Hänen ehdollinen sarake on todennäköisesti lyhin.
Mutta Kenin suosima ratkaisu jättää alkuperäisen kysymyksen huomiotta. Taulun luomisen sijaan Power Queryssä hän luo käännettävän tietojoukon Power Queryssä ja viimeistelee sitten pivot-taulukon.
Kenin viimeinen esikatselu Power Queryssä näyttää tältä:
Tässä on Kenin koodi:
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"Kun tämä kysely on luotu vain yhteydeksi, hän käyttää sitten pivot-taulukkoa lopullisen raportin luomiseen.
Muiden MVP: n ratkaisut:
- Wyn Hopkinsin koodi on täällä: Power Query: Useiden identtisten otsikkojen käsittely.
- Mike Girvinin koodi on täällä: Power Query: Poimitaan 2 vasenta merkkiä sarakkeesta.
- Roger Govierin kaavan ratkaisu on täällä: Formula Solutions.
Palaa Podcast 2316 -haasteen pääsivulle.
Lue tämän sarjan seuraava artikkeli: Power Query: Beyond the User Interface: Table.Split and More.
