Les MVP Excel attaquent le problème de nettoyage des données dans Power Query - Conseils Excel

Table des matières

Remarque

Ceci fait partie d'une série d'articles détaillant les solutions envoyées pour le défi Podcast 2316.

Le MVP Excel Oz Du Soleil de la chaîne Excel on Fire sur YouTube a mentionné le Bull Rider brésilien Kaique Pachecho. Oz a été la première personne à remarquer que je suis allé lentement pour ajouter les quatre quarts.

La vidéo d'Oz est:
https://www.youtube.com/watch?v=OluZlF44PNI

Son code est:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Removed Columns" = Table.RemoveColumns(Source,("Column2", "Column3", "Column4", "Column5", "Column6")), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Administrative", type number), ("Holiday", Int64.Type), ("PTO/LOA/Jury Duty", Int64.Type), ("Project A", type number), ("Project B", type number), ("Project C", type number))), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if (Category Description) = "Q1" then null else if (Category Description) = "Q2" then null else if (Category Description) = "Q3" then null else if (Category Description) = "Q4" then null else (Category Description)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Custom")), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",(("Custom", "Names"))), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each (Category Description) = "Q1" or (Category Description) = "Q2" or (Category Description) = "Q3" or (Category Description) = "Q4"), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",("Names", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Names", "Category Description"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(#"Category Description")), "Category Description", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Sum",(("Addition", "TOTAL"))) in #"Renamed Columns1"

Autre solution, celle-ci de Excel MVP John MacDougall.

  • John a été le premier à dire qu'en supprimant les deux étapes supplémentaires ajoutées par Power Query, vous supprimez les suffixes impairs sur les en-têtes Q1 Q2 Q3 Q4 en double.
  • John a utilisé une colonne Index au début qui serait utilisée à la fin pour le tri. Mais - John a concaténé sa colonne d'index après la description de la catégorie. Il a utilisé un caractère de tuyau vertical | pour qu'il puisse sortir les données plus tard.
  • John a tapé sa colonne conditionnelle comme une colonne personnalisée au lieu d'utiliser l'interface de colonne conditionnelle.
Colonne conditionnelle en tant que colonne personnalisée

Regardez la vidéo de John ici:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

Excel MVP Ken Puls, co-auteur du livre M is for (Data) Monkey envoyé en trois solutions. Sa chronique conditionnelle est probablement la plus courte.

Mais la solution préférée de Ken ignore la question initiale. Au lieu de créer la table dans Power Query, il crée un jeu de données pivotant dans Power Query, puis termine avec un tableau croisé dynamique.

L'aperçu final de Ken dans Power Query ressemble à ceci:

Ensemble de données pivotant

Voici le code de Ken:

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))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",("Dept. Total", "Q1", "Q2", "Q3", "Q4")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", ("Category Description"), "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if Text.Contains((Attribute), "_") then null else (Attribute)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "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))), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ((Attribute.2) null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",(("Attribute.1", "Quarter"), ("Value", "Amount"))), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",(("Category Description", type text), ("Quarter", type text), ("Amount", type number), ("Employee", type text))) in #"Changed Type2"

Après avoir créé cette requête en tant que connexion uniquement, il utilise ensuite un tableau croisé dynamique pour créer le rapport final.

Rapport final avec tableau croisé dynamique

Solutions d'autres MVP:

  • Le code de Wyn Hopkins est ici: Power Query: Gérer plusieurs en-têtes identiques.
  • Le code de Mike Girvin est ici: Power Query: Extraction de 2 caractères de gauche à partir d'une colonne.
  • La solution de formule de Roger Govier est ici: Formula Solutions.

Revenez à la page principale du défi Podcast 2316.

Lisez l'article suivant de cette série: Power Query: au-delà de l'interface utilisateur: Table.Split et plus.

Articles intéressants...