Als ik een live Power Excel-seminar geef, bied ik aan dat als iemand in de kamer ooit een vreemd Excel-probleem heeft, ze het mij voor hulp kunnen sturen. Dat is hoe ik dit probleem met het opschonen van gegevens kreeg. Iemand had een samenvattend werkblad dat er als volgt uitziet:
Ze wilden de gegevens opnieuw formatteren om er als volgt uit te zien:
Een interessante aanwijzing over deze gegevens: de 18 in G4 lijkt een subtotaal te zijn van H4: K4. Het is verleidelijk om de kolommen G, L, enzovoort te verwijderen, maar u moet eerst de naam van de werknemer uit G3, L3 enzovoort halen.
Het was 4 uur 's ochtends op zondag 9 februari toen ik de videorecorder aanzette en een paar onhandige stappen opnam in Power Query om het probleem op te lossen. Aangezien het zondag was, een dag waarop ik normaal gesproken geen video's maak, heb ik mensen gevraagd om hun ideeën in te sturen om het probleem op te lossen. Er zijn 29 oplossingen ingezonden.
Elke oplossing biedt een coole nieuwe verbetering ten opzichte van mijn proces. Mijn plan is om een serie artikelen te starten die de verschillende verbeteringen aan mijn methode laten zien.
Bekijk video
Voordat ik aan dat proces begin, nodig ik je uit om mijn oplossing te zien:
En de M-code die Power Query voor mij heeft gegenereerd:
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"Voordat we beginnen met het bespreken van de oplossingen, laten we eerst een groot aantal veelvoorkomende opmerkingen bespreken:
- Sommigen van jullie zeiden dat je terug zou gaan om erachter te komen waarom de gegevens om te beginnen in dit formaat worden weergegeven. Ik waardeer deze opmerkingen. Iedereen die zei dat dit een beter persoon is dan ik. Ik heb in de loop der jaren geleerd dat wanneer je vraagt "Waarom?" het antwoord betreft meestal deze voormalige werknemer die 17 jaar geleden op deze weg is begonnen en iedereen blijft het op deze manier gebruiken omdat we er nu allemaal aan gewend zijn.
- Velen van jullie zeiden ook dat de uiteindelijke oplossing een hoge verticale tafel zou moeten zijn en vervolgens een draaitabel zou moeten gebruiken om de uiteindelijke resultaten te produceren. Jonathan Cooper vatte dit het beste samen: "Ik ben het ook eens met enkele van de andere YouTube-opmerkingen dat een goede gegevensset geen" Totalen "zou hebben en aan het einde niet hoeft te worden gedraaid. Maar als de gebruiker echt een duidelijke oude tafel, dan geef je ze wat ze willen. " Ik kan hier eigenlijk beide kanten van zien. Ik ben dol op een draaitabel en het enige dat leuker is dan Power Query is Power Query met een mooie draaitabel erop. Maar als we het hele ding kunnen doen in Power Query, dan één ding minder om te breken.
Hier zijn hyperlinks naar verschillende technieken
-
Power Query-technieken
- Nummering van groepen records
- Twee linker karakters extraheren
- Totale kolom
- Anders als clausules
- Meerdere identieke kopteksten in Power Query
- Wat te verwijderen
- Gesplitst door Q
- Regelitems sorteren
- Power Query-oplossingen van Excel MVP's
-
Verder gaan dan de Power Query-interface
- Tafel.Split
- De wereld van Bill Szysz
-
Formule-oplossingen
- Eén dynamische matrixformule
- Old School Helper Columns
- Formule-oplossingen
-
Samengesteld uit alle ideeën van bovenaf en uiteindelijke video
- Samenstelling van de beste ideeën van allemaal
