Lorsque je fais un séminaire Power Excel en direct, je propose que si quelqu'un dans la salle a un problème Excel étrange, il peut me l'envoyer pour obtenir de l'aide. C'est ainsi que j'ai reçu ce problème de nettoyage des données. Quelqu'un avait une feuille de calcul récapitulative qui ressemble à ceci:
Ils voulaient reformater les données pour ressembler à ceci:
Un indice intéressant à propos de ces données: le 18 dans G4 semble être un sous-total de H4: K4. Il est tentant de supprimer les colonnes G, L, etc., mais vous devez d'abord extraire le nom de l'employé de G3, L3, etc.
Il était 4 heures du matin le dimanche 9 février lorsque j'ai allumé le magnétoscope et enregistré quelques étapes maladroites dans Power Query pour résoudre le problème. Étant donné que c'était dimanche, un jour où je ne fais normalement pas de vidéos, j'ai demandé aux gens d'envoyer leurs idées sur la façon de résoudre le problème. 29 solutions ont été envoyées.
Chaque solution offre de nouvelles améliorations intéressantes par rapport à mon processus. Mon plan est de commencer une série d'articles qui montrent les différentes améliorations apportées à ma méthode.
Regarder la vidéo
Avant de commencer ce processus, je vous invite à voir ma solution:
Et le code M que Power Query a généré pour moi:
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))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "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))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",(("Attribute.1", "TextValue"))), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length((TextValue))), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if (Length)> 2 then (TextValue) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Name")), #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",("Category Description", "Name", "TextValue", "Value", "Length")), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ((TextValue) = "Q1" or (TextValue) = "Q2" or (TextValue) = "Q3" or (TextValue) = "Q4")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each (Name) "Dept. Total"), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Length")), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"(TextValue)), "TextValue", "Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Name", Order.Ascending))), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each (Q1)+(Q2)+(Q3)+(Q4)) in #"Added Custom1"Avant de commencer à entrer dans les solutions, abordons de nombreux commentaires courants:
- Certains d'entre vous ont dit que vous reviendriez en arrière pour comprendre pourquoi les données apparaissent dans ce format pour commencer. J'apprécie ces commentaires. Tous ceux qui ont dit que c'était une meilleure personne que moi. J'ai appris au fil des ans que lorsque vous demandez "Pourquoi?" la réponse implique généralement cet ancien employé qui a commencé dans cette voie il y a 17 ans et tout le monde continue de l'utiliser de cette façon puisque nous y sommes tous habitués maintenant.
- De plus - beaucoup d'entre vous - ont déclaré que la solution finale devrait être un grand tableau vertical, puis utiliser un tableau croisé dynamique pour produire les résultats finaux. Jonathan Cooper a parfaitement résumé ceci: "Je suis également d'accord avec certains des autres commentaires de YouTube selon lesquels un ensemble de données approprié n'aurait pas de" Totaux "et n'aurait pas besoin d'être pivoté à la fin. Mais si l'utilisateur veut vraiment un simple vieille table alors vous leur donnez ce qu'ils veulent. " Je peux en fait voir les deux côtés de cela. J'adore un tableau croisé dynamique et la seule chose plus amusante que Power Query est Power Query avec un joli tableau croisé dynamique sur le dessus. Mais si nous pouvons tout faire dans Power Query, alors une chose de moins à casser.
Voici des hyperliens vers diverses techniques
-
Techniques Power Query
- Numérotation des groupes d'enregistrements
- Extraction de deux caractères de gauche
- Colonne totale
- Sinon si les clauses
- Plusieurs en-têtes identiques dans Power Query
- Que supprimer
- Divisé par Q
- Tri des éléments de campagne
- Solutions Power Query d'Excel MVP
-
Au-delà de l'interface Power Query
- Table.Split
- Le monde de Bill Szysz
-
Solutions de formule
- Une formule de matrice dynamique
- Colonnes d'aide de la vieille école
- Solutions de formule
-
Composite de toutes les idées d'en haut et vidéo finale
- Composé des meilleures idées de tous
