Uwaga
Jest to jeden z serii artykułów opisujących szczegółowo rozwiązania przesłane w ramach wyzwania Podcast 2316.
W moim pierwotnym problemie z kształtowaniem danych napotkałem problem na bardzo wczesnym etapie procesu. Przychodzące dane miały mieć wiele kolumn z nagłówkiem Q1.
W moim rozwiązaniu utworzyłem nazwany zakres „UglyData” i zaimportowałem go do dodatku Power Query. Doprowadziło to do niezadowalającego wyniku zmiany nazwy moich kolumn przez dodatek Power Query na Q1_1.
Później, po cofnięciu obrotu, musiałem wyodrębnić tylko dwie lewe znaki z tych nagłówków.
Istnieją trzy oddzielne rozwiązania tego problemu:
- Wyn Hopkins i Obniżenie nagłówków
- MF Wong i odznacz My Table Has Headers (również sugerowane przez Petera Bartholomew)
- Jason M i po prostu usuń Promoted Headers (również zasugerowane przez Ondřeja Malinskiego i Excel MVP Johna MacDougalla)
Pierwsza innowacja pochodziła od Wyn Hopkinsa z Access Analytic. Zamiast nazwanego zakresu, Wyn przekonwertował dane na tabelę za pomocą Ctrl + T.W tym momencie uszkodzenie nagłówków zostało zrobione, ponieważ Excel przekonwertował nagłówki na:
Gdy Wyn przeniósł dane do Power Query, otworzył menu rozwijane Użyj pierwszego wiersza jako nagłówków i wybrał opcję Użyj nagłówków jako pierwszego wiersza. Nigdy nie zdawałem sobie sprawy, że to tam jest. Tworzy krok o nazwie Table.DemoteHeaders.
Ale nawet z poprawą Wyna nadal musiałby później wyodrębnić pierwsze 2 znaki z tych nagłówków.
Drugą innowacją jest technika MF Wonga. Kiedy stworzył tabelę, odznaczył opcję Moja tabela ma nagłówki!
Dzięki temu program Excel pozostawia same nagłówki Q1 i nie ma potrzeby późniejszego wyodrębniania dodatkowego sufiksu.
Rozumiem, że w obozie „Uwielbiam stoły” są ludzie. Film MF Wonga pokazał, jak może dodawać nowych pracowników po prawej stronie danych, a tabela automatycznie się rozszerza. Istnieje wiele dobrych powodów, dla których warto używać tabel.
Ale ponieważ uwielbiam podsumowania, widoki niestandardowe i filtrowanie według wyboru, zwykle nie używam tabel. Doceniam więc rozwiązanie Jasona M. Zachował dane jako nazwany zakres UglyData. Zaraz po zaimportowaniu danych do dodatku Power Query usunął te dwa kroki:
Teraz, gdy dane znajdują się po prostu w wierszu 1, nie ma problemu z posiadaniem wielu kolumn o nazwie Q1.
Oto kod Wyna Hopkina pokazujący DemotedHeaders:
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"Wróć do strony głównej wyzwania Podcast 2316.
Przeczytaj następny artykuł z tej serii: Power Query: usunąć to, usunąć te lub nic nie usunąć ?.
