Remarque
Ceci fait partie d'une série d'articles détaillant les solutions envoyées pour le défi Podcast 2316.
Dans mon problème initial de mise en forme des données, j'ai rencontré un problème très tôt dans le processus. Les données entrantes allaient avoir de nombreuses colonnes avec un en-tête de Q1.
Dans ma solution, j'ai créé une plage nommée «UglyData» et l'ai importée dans Power Query. Cela a conduit au résultat malheureux de Power Query renommant mes colonnes en Q1_1.
Plus tard, après un pivotement, j'ai dû extraire uniquement les deux caractères de gauche de ces en-têtes.
Il y avait trois solutions distinctes à ce problème:
- Wyn Hopkins et rétrograder les en-têtes
- MF Wong et décochez My Table Has Headers (également suggéré par Peter Bartholomew)
- Jason M et supprimez simplement les en-têtes sponsorisés (également suggéré par Ondřej Malinský et Excel MVP John MacDougall)
La première innovation vient de Wyn Hopkins d'Access Analytic. Au lieu d'une plage nommée, Wyn a converti les données en une table en utilisant Ctrl + T.À ce stade, les dommages aux en-têtes ont été causés, car Excel a converti les en-têtes en:
Une fois que Wyn a pris les données dans Power Query, il a ensuite ouvert le menu déroulant Utiliser la première ligne comme en-têtes et a choisi Utiliser les en-têtes comme première ligne. Je n'ai jamais réalisé que c'était là. Il crée une étape appelée Table.DemoteHeaders.
Mais, même avec l'amélioration de Wyn, il devrait encore plus tard extraire les 2 premiers caractères de ces en-têtes.
La deuxième innovation est la technique de MF Wong. Quand il a créé la table, il a décoché Ma table a des en-têtes!
Cela garantit qu'Excel laisse les multiples en-têtes Q1 seuls et qu'il n'est pas nécessaire d'extraire le suffixe supplémentaire plus tard.
Je comprends qu'il y a des gens dans le camp «J'aime les tables». La vidéo de MF Wong a montré comment il pouvait ajouter de nouveaux employés à droite des données et le tableau se développe automatiquement. Il existe de nombreuses bonnes raisons d'utiliser des tableaux.
Mais, parce que j'aime les sous-totaux, les vues personnalisées et le filtre par sélection, j'ai tendance à ne pas utiliser de tableaux. Donc, j'apprécie la solution de Jason M. Il a conservé les données comme plage nommée de UglyData. Dès qu'il a importé les données dans Power Query, il a supprimé ces deux étapes:
Maintenant, avec les données simplement dans la ligne 1, il n'y a pas de problème avec de nombreuses colonnes appelées Q1.
Voici le code de Wyn Hopkin montrant 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"Revenez à la page principale du défi Podcast 2316.
Lisez l'article suivant de cette série: Power Query: supprimer ceci, les supprimer ou ne rien supprimer ?.
