„Excel“ MVP užpuolė duomenų valymo problemą „Power Query“ - „Excel“ patarimai

Turinys

Pastaba

Tai vienas iš straipsnių serijos, kuriame pateikiami išsamūs sprendimai, atsiųsti iššūkiui „Podcast 2316“.

„Excel MVP“ Oz Du Soleil iš „Excel on Fire“ kanalo „YouTube“ paminėjo Brazilijos bulių lenktynininką Kaique Pachecho. Ozas buvo pirmasis asmuo, pastebėjęs, kad ėjau lėtu keliu keturis ketvirčius.

Ozo vaizdo įrašas yra:
https://www.youtube.com/watch?v=OluZlF44PNI

Jo kodas yra:

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"

Kitas sprendimas, šis iš „Excel MVP“ John MacDougall.

  • Jonas pirmasis pasakė, kad ištrynę du papildomus veiksmus, kuriuos pridėjo „Power Query“, pašalinsite nelyginius sufiksus Q1 Q2 Q3 Q4 antraštėse.
  • Jonas anksti naudojo „Index“ stulpelį, kuris bus naudojamas pabaigoje. Bet - Jonas sujungė savo indekso stulpelį po kategorijos aprašymo. Jis panaudojo vertikalų vamzdžio simbolį | kad vėliau jis galėtų išskaidyti duomenis.
  • Jonas įrašė savo sąlyginį stulpelį kaip pasirinktinį stulpelį, užuot naudojęs Sąlyginio stulpelio sąsają.
Sąlyginis stulpelis kaip pasirinktinis stulpelis

Žiūrėkite Jono vaizdo įrašą čia:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

„Excel MVP“ Ken Puls, „M“ bendraautorius, skirtas „Duomenų“ beždžionių knygai, išsiųstai trimis sprendimais. Jo sąlyginė kolona yra bene trumpiausia.

Tačiau Keno pageidaujamas sprendimas nepaiso pirminio klausimo. Užuot sukūręs lentelę „Power Query“, jis sukuria pasukamą duomenų rinkinį „Power Query“ ir baigia naudodamasis suvestine lentele.

Paskutinė Keno peržiūra „Power Query“ atrodo taip:

Pasukamas duomenų rinkinys

Štai Keno kodas:

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"

Sukūręs šią užklausą tik kaip ryšį, jis, naudodamas sukamą lentelę, sukuria galutinę ataskaitą.

Galutinė ataskaita su „Pivot“ lentele

Kitų MVP sprendimai:

  • Wyn Hopkins kodas yra čia: „Power Query“: darbas su keletu identiškų antraščių.
  • Mike'o Girvino kodas yra čia: „Power Query“: iš kairės 2 simbolių išskyrimas iš stulpelio.
  • Rogerio Goviero formulės sprendimas yra čia: „Formulės sprendimai“.

Grįžkite į pagrindinį „Podcast 2316“ iššūkio puslapį.

Perskaitykite kitą šios serijos straipsnį: „Power Query“: už vartotojo sąsajos ribų: lentelė. Skirstymas ir dar daugiau.

Įdomios straipsniai...