Excel MVP uzbrūk datu attīrīšanas problēmai enerģijas vaicājumā - Excel padomi

Satura rādītājs

Piezīme

Šis ir viens no rakstu sērijas, kurā sīki aprakstīti Podcast 2316 izaicinājumam nosūtītie risinājumi.

Excel MVP Oz Du Soleil no Excel on Fire kanāla YouTube pieminēja brazīliešu buļļu braucēju Kaique Pachecho. Ozs bija pirmais, kurš pamanīja, ka es gāju lēnu ceļu, lai pievienotu četras ceturtdaļas.

Oza video ir:
https://www.youtube.com/watch?v=OluZlF44PNI

Viņa kods ir:

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"

Vēl viens risinājums, šis ir no Excel MVP John MacDougall.

  • Džons bija pirmais, kurš, izdzēšot pievienotās divas papildu darbības, kas saistītas ar Power Query, izslēdz dīvainos sufiksus dublikātu Q1 Q2 Q3 Q4 virsrakstos.
  • Jānis agri izmantoja Index kolonnu, kuru beigās izmantos šķirošanai. Bet - Džons saīsināja indeksu kolonnu pēc kategorijas apraksta. Viņš izmantoja vertikālu caurules raksturu | lai viņš vēlāk varētu izdalīt datus.
  • Jānis ierakstīja savu nosacīto kolonnu kā pielāgotu kolonnu, nevis izmantoja saskarni Nosacītā kolonna.
Nosacījuma kolonna kā pielāgota kolonna

Noskatieties Jāņa videoklipu šeit:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

Excel MVP Ken Puls, M līdzautors, ir paredzēts (Datu) Pērtiķu grāmatai, kas nosūtīta trīs risinājumos. Viņa nosacītā kolonna, iespējams, ir īsākā.

Bet Kena vēlamais risinājums ignorē sākotnējo jautājumu. Tā vietā, lai Power Query izveidotu tabulu, viņš Power Query izveido grozāmu datu kopu un pēc tam pabeidz ar pagrieziena tabulu.

Kena pēdējais priekšskatījums programmā Power Query izskatās šādi:

Pagriežama datu kopa

Šeit ir Kena kods:

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"

Izveidojis šo vaicājumu tikai kā savienojumu, viņš pēc tam izmanto pagrieziena tabulu, lai izveidotu galīgo pārskatu.

Galīgais ziņojums ar rakurstabulu

Risinājumi no citiem MVP:

  • Vina Hopkinsa kods ir šeit: Power Query: Darījumu ar vairākām identiskām galvenēm.
  • Maika Girvina kods ir šeit: Power Query: kreisās 2 rakstzīmes tiek izvilktas no kolonnas.
  • Rodžera Govjē formulas risinājums ir šeit: Formulas risinājumi.

Atgriezieties uz Podcast 2316 izaicinājuma galveno lapu.

Izlasiet nākamo šīs sērijas rakstu: Power Query: Beyond User Interface: Table.Split and More.

Interesanti raksti...