När jag gör ett live Power Excel-seminarium erbjuder jag att om någon i rummet någonsin har ett udda Excel-problem, kan de skicka det till mig för hjälp. Det var så jag fick detta problem med datarengöring. Någon hade ett sammanfattande kalkylblad som ser ut så här:
De ville omformatera uppgifterna så att de ser ut så här:
En intressant ledtråd om dessa data: 18 i G4 verkar vara en delsumma av H4: K4. Det är frestande att ta bort kolumnerna G, L och så vidare, men först måste du extrahera medarbetarnamnet från G3, L3 och så vidare.
Det var klockan 4 på söndagen den 9 februari när jag slog på videobandspelaren och spelade in några klumpiga steg i Power Query för att lösa problemet. Med tanke på att det var söndag, en dag som jag normalt inte gör videor, bad jag folk att skicka in sina idéer om hur man löser problemet. 29 lösningar har skickats in.
Varje lösning erbjuder en cool ny förbättring jämfört med min process. Min plan är att starta en serie artiklar som visar de olika förbättringarna av min metod.
Kolla på video
Innan jag börjar den processen uppmanar jag dig att se min lösning:
Och M-koden som Power Query genererade för mig:
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))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "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))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",(("Attribute.1", "TextValue"))), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length((TextValue))), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if (Length)> 2 then (TextValue) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Name")), #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",("Category Description", "Name", "TextValue", "Value", "Length")), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ((TextValue) = "Q1" or (TextValue) = "Q2" or (TextValue) = "Q3" or (TextValue) = "Q4")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each (Name) "Dept. Total"), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Length")), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"(TextValue)), "TextValue", "Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Name", Order.Ascending))), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each (Q1)+(Q2)+(Q3)+(Q4)) in #"Added Custom1"Innan vi börjar ta del av lösningarna, låt oss ta upp många vanliga kommentarer:
- Några av er sa att du skulle gå bakåt för att ta reda på varför uppgifterna dyker upp till att börja med. Jag uppskattar dessa kommentarer. Alla som sa att det här är en bättre person än jag. Jag har lärt mig genom åren att när du frågar "Varför?" svaret involverar vanligtvis denna tidigare anställd som började på den här vägen för 17 år sedan och alla fortsätter att använda den på det här sättet eftersom vi alla är vana vid det nu.
- Dessutom - många av er - sa att den slutliga lösningen borde vara en hög vertikal tabell och sedan använda en pivottabell för att få de slutliga resultaten. Jonathan Cooper sammanfattade detta bäst: "Jag håller också med några av de andra YouTube-kommentarerna om att en korrekt datamängd inte skulle ha" Totals "och inte skulle behöva svängas i slutet. Men om användaren verkligen vill ha en vanlig gammalt bord så ger du dem vad de vill ha. " Jag kan faktiskt se båda sidor av detta. Jag älskar ett pivottabell och det enda roligare än Power Query är Power Query med ett trevligt pivottabell på toppen. Men om vi kan göra det hela i Power Query, då en sak mindre att bryta.
Här är hyperlänkar till olika tekniker
-
Power Query-tekniker
- Numreringsgrupper av poster
- Extrahera vänster två tecken
- Total kolumn
- Annars om klausuler
- Flera identiska rubriker i Power Query
- Vad du ska ta bort
- Dela med Q
- Sortera rader
- Power Query Solutions från Excel MVPs
-
Flyttar sig bortom Power Query Interface
- Tabell. Split
- The World Of Bill Szysz
-
Formellösningar
- En dynamisk matrisformel
- Old School Helper Columns
- Formellösningar
-
Sammansatt av alla idéer från ovan och slutlig video
- Sammansatt av de bästa idéerna från alla
