Remarque
Ceci fait partie d'une série d'articles détaillant les solutions envoyées pour le défi Podcast 2316.
L'un des problèmes avec ma solution est que la séquence finale des catégories ne correspondait pas nécessairement à la séquence d'origine des colonnes. Je m'en suis rendu compte à la toute fin de ma vidéo, et comme ce n'était pas particulièrement important, je ne m'en souciais pas.
Cependant, Josh Johnson a envoyé une solution qui l'a géré. Quand Josh a dit qu'il utilisait une colonne Index, j'ai supposé que c'était comme l'index et le module dans Power Query: Number Groups of Records comme 1 à 5 à plusieurs reprises. Mais l'utilisation de Josh était complètement différente.
Remarque: le MVP Excel John MacDougall a également utilisé cette méthode, mais il a concaténé la colonne d'index à la fin de la description de la catégorie. Voir la vidéo de John ici: https://www.youtube.com/watch?v=Dqmb6SEJDXI et en savoir plus sur son code ici: Excel MVP attaque le problème de nettoyage des données dans Power Query.
Au début du processus, alors que Josh n'avait encore que six enregistrements, il a ajouté un index commençant à 1. Josh a cliqué dans la barre de formule et a renommé la colonne Index en Catégorie.
La colonne Catégorie était la nouvelle dernière colonne. Il a utilisé Move, to Beginning pour le déplacer pour être le premier:
Après cela, de nombreuses autres étapes se produisent. Ce sont des étapes innovantes mais qui ont été pour la plupart couvertes jusqu'à présent dans les autres articles. Après de nombreuses étapes de ce genre, je commençais à penser que les numéros de catégorie 1 à 6 n'étaient qu'une erreur. Je pensais que Josh allait peut-être les supprimer sans les utiliser.
Josh Unpivots, puis la colonne conditionnelle, puis remplissez, puis pivote, ajoute le total. Il ne semble jamais utiliser cette colonne de catégorie. Après de nombreuses étapes, il est là:
Mais ensuite, dans les dernières étapes, Josh trie les données par nom d'employé puis par catégorie!
À ce stade, il peut supprimer la colonne Catégorie. La dernière différence: PTO vient avant le projet A, tout comme il l'avait fait dans les colonnes d'origine. C'est une délicate attention.
Je soulignerai également que Josh a envoyé une vidéo de lui passant par ces étapes. Félicitations à Josh pour avoir utilisé les raccourcis clavier dans Power Query!
Voici le code de Josh:
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))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"Revenez à la page principale du défi Podcast 2316.
Lisez l'article suivant de cette série: Les MVP Excel attaquent le problème de nettoyage des données dans Power Query.
