Opmerking
Dit is een van een reeks artikelen over oplossingen die zijn ingezonden voor de Podcast 2316-uitdaging.
Bij mijn oorspronkelijke datavormingsprobleem kwam ik al heel vroeg in het proces een probleem tegen. De inkomende gegevens zouden veel kolommen hebben met de kop Q1.
In mijn oplossing heb ik een genoemd bereik "UglyData" gemaakt en dat geïmporteerd in Power Query. Dit leidde tot het ongelukkige resultaat dat Power Query mijn kolommen hernoemde naar Q1_1.
Later, na het ongedaan maken van de draaiing, moest ik alleen de linker twee tekens uit die kopteksten halen.
Er waren drie afzonderlijke oplossingen voor dit probleem:
- Wyn Hopkins en Degraderen Headers
- MF Wong en schakel My Table Has Headers uit (ook voorgesteld door Peter Bartholomew)
- Jason M en verwijder gewoon Promoted Headers (ook voorgesteld door Ondřej Malinský en Excel MVP John MacDougall)
De eerste innovatie was van Wyn Hopkins van Access Analytic. In plaats van een benoemd bereik, converteerde Wyn de gegevens naar een tabel met Ctrl + T.Op dit punt werd de schade aan de koppen aangericht, aangezien Excel de koppen converteerde naar:
Toen Wyn de gegevens eenmaal in Power Query had opgenomen, opende hij het vervolgkeuzemenu Eerste rij als koptekst gebruiken en koos Kopteksten als eerste rij gebruiken. Ik heb me nooit gerealiseerd dat dit er was. Het creëert een stap met de naam Table.DemoteHeaders.
Maar zelfs met Wyn's verbetering zou hij later nog steeds de eerste 2 karakters uit die headers moeten halen.
De tweede innovatie is de techniek van MF Wong. Toen hij de tafel maakte, verwijderde hij het vinkje bij My Table Has Headers!
Dit zorgt ervoor dat Excel de meerdere Q1-headers met rust laat en dat het niet nodig is om het extra achtervoegsel later te extraheren.
Ik begrijp dat er mensen zijn in het "I love tables" -kamp. De video van MF Wong liet zien hoe hij rechts van de gegevens nieuwe medewerkers kon toevoegen en de tabel wordt automatisch uitgevouwen. Er zijn veel goede redenen om tabellen te gebruiken.
Maar omdat ik dol ben op subtotalen, aangepaste weergaven en filteren op selectie, gebruik ik meestal geen tabellen. Dus ik waardeer de oplossing van Jason M. Hij bewaarde de gegevens als het genoemde bereik van UglyData. Zodra hij de gegevens in Power Query had geïmporteerd, heeft hij deze twee stappen verwijderd:
Nu de gegevens gewoon in rij 1 staan, is er geen gedoe met veel kolommen die Q1 worden genoemd.
Hier is de code van Wyn Hopkin die DemotedHeaders laat zien:
let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Demoted Headers1" = Table.DemoteHeaders(Source), #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"), #"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each if Text.Start((Column1),1) = "Q" then null else (Column1)), #"Filled Down" = Table.FillDown(#"Added Custom",("Custom")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Custom) "Dept. Total")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith((Column1), "Employee")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", (PromoteAllScalars=true)), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", (("Category Description", each Text.Start(_, 2), type text))), #"Reordered Columns" = Table.ReorderColumns(#"Extracted First Characters",("Category Description_1", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category Description_1", "Category Description"), "Attribute", "Value"), #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",("Category Description_1", "Attribute", "Category Description", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"(#"Category Description")), "Category Description", "Value", List.Sum), #"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",("Attribute", "Category Description_1", "Q1", "Q2", "Q3", "Q4")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",(("Attribute", "Cat Deasc"), ("Category Description_1", "Emp Name"))), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",(("Emp Name", type text), ("Q1", Int64.Type), ("Q2", Int64.Type), ("Q3", Int64.Type), ("Q4", Int64.Type))), #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), Int64.Type) in #"Inserted Sum"Keer terug naar de hoofdpagina voor de Podcast 2316-uitdaging.
Lees het volgende artikel in deze serie: Power Query: dit verwijderen, die verwijderen of niets verwijderen ?.
