Solution composite au défi Podcast 2316 - Astuces Excel

Remarque

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

Après avoir étudié toutes les idées envoyées par les téléspectateurs, j'ai choisi mes techniques préférées à partir de chaque vidéo. Ma solution finale utilise ces étapes:

• Obtenir des données, à partir de la plage nommée
• Supprimez les deux étapes supplémentaires ajoutées à Promouvoir les en-têtes et Modifier le type. Cela évite d'avoir à casser le suffixe des quartiers. Merci à Jason M, Ondřej Malinský et Peter Bartholomew pour cette idée.
• Transposer
• Promouvoir les en-têtes
• Supprimer, les premières lignes, les 5 premières lignes. Belle astuce de MF Wong.
• Remplacez Q1 par _Q1. Répétez pour les trois autres quarts. Merci Jonathan Cooper.
• Fractionner par délimiteur au _. Cette étape étonnante conserve les noms dans une colonne et déplace les trimestres vers la colonne suivante. Proposé par Fowmy, perfectionné par Jonathan Cooper.
• (Pas une étape!) Accédez à la barre de formule et renommez les colonnes en Employé et Trimestre. Merci Josh Johnson
• Dans la colonne Employé, ne remplacez rien par null
• Remplissez
• Dans la colonne Trimestre, remplacez null par Total. Cette idée de Michael Karpfen
• Annuler le pivot d'autres colonnes. Renommer l'attribut en catégorie dans la barre de formule
• Quartiers de pivot
• Déplacer la colonne totale à la fin

Voici mon code final:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Transposed Table" = Table.Transpose(Source), #"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))), #"Removed Top Rows" = Table.Skip(#"Changed Type",5), #"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows","Q1","_Q1",Replacer.ReplaceText,("Category Description")), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Q2","_Q2",Replacer.ReplaceText,("Category Description")), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Q3","_Q3",Replacer.ReplaceText,("Category Description")), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Q4","_Q4",Replacer.ReplaceText,("Category Description")), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value3", "Category Description", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), ("Employee", "Qtr")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Employee", type text), ("Qtr", type text))), #"Replaced Value4" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,("Employee")), #"Filled Down" = Table.FillDown(#"Replaced Value4",("Employee")), #"Replaced Value5" = Table.ReplaceValue(#"Filled Down",null,"Total",Replacer.ReplaceValue,("Qtr")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value5", ("Employee", "Qtr"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(Qtr)), "Qtr", "Value", List.Sum), #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",("Employee", "Attribute", "Q1", "Q2", "Q3", "Q4", "Total")) in #"Reordered Columns"

Toutes les personnes mentionnées dans ces articles ou vidéos remportent un correctif Excel Guru. J'en ai déjà envoyé plusieurs. Si vous n'en recevez pas, laissez un commentaire sur la vidéo ci-dessous.