Excel MVP-ji napadajo problem čiščenja podatkov v Power Query - Nasveti za Excel

Kazalo

Opomba

To je eden iz serije člankov, ki podrobno opisujejo rešitve, poslane za izziv Podcast 2316.

Excel MVP Oz Du Soleil iz kanala Excel on Fire na YouTubu je omenil brazilskega Bull Riderja Kaiqueja Pachecha. Oz je bil prvi, ki je opazil, da sem šel počasi in dodal štiri četrtine.

Ozov video je:
https://www.youtube.com/watch?v=OluZlF44PNI

Njegova koda je:

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"

Druga rešitev, ta od Excelovega MVP Johna MacDougalla.

  • John je bil prvi, ki je rekel, da z izbrisom dveh dodanih dodatnih korakov Power Query odstranite nenavadne končnice na podvojenih naslovih Q1 Q2 Q3 Q4.
  • Janez je zgodaj uporabil stolpec Indeks, ki bi ga uporabili na koncu za razvrščanje. Toda - John je po opisu kategorije združil svoj indeksni stolpec. Uporabil je znak navpične cevi | da bi lahko podatke razdelil kasneje.
  • John je vnesel svoj pogojni stolpec kot stolpec po meri, namesto da bi uporabil vmesnik pogojnega stolpca.
Pogojni stolpec kot stolpec po meri

Oglejte si Johnov video tukaj:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

Excel MVP Ken Puls, soavtor knjige M is for (Data) Monkey book, poslane v treh rešitvah. Njegova pogojna rubrika je verjetno najkrajša.

Toda Kenova najprimernejša rešitev ignorira prvotno vprašanje. Namesto da ustvari tabelo v Power Query, v Power Query ustvari premični nabor podatkov in nato zaključi z vrtilno tabelo.

Končni predogled Kena v Power Query je videti takole:

Premični nabor podatkov

Tu je Kenova koda:

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"

Ko ustvari to poizvedbo samo kot povezavo, nato z vrtilno tabelo ustvari končno poročilo.

Končno poročilo s pivot tabelo

Rešitve drugih MVP-jev:

  • Koda Wyn Hopkins je tukaj: Power Query: Obravnavanje več enakih glav.
  • Koda Mikea Girvina je tukaj: Power Query: pridobivanje levih 2 znakov iz stolpca.
  • Rešitev formule Rogerja Govierja je tukaj: Formula Solutions.

Vrnite se na glavno stran izziva Podcast 2316.

Preberite naslednji članek v tej seriji: Power Query: Beyond the User Interface: Table.Split in še več.

Zanimive Članki...