Remarque
Ceci fait partie d'une série d'articles détaillant les solutions envoyées pour le défi Podcast 2316.
Dans mon Power Query Challenge, l'une des étapes consistait à prendre le champ de nom de chaque cinquième enregistrement et à le copier dans les cinq enregistrements. Ma solution originale était maladroite, en comptant sur le fait que la longueur du nom serait plus longue que 2 caractères.
Plusieurs personnes, dont MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers ont utilisé une bien meilleure solution impliquant une colonne Index.
Reprenons le processus où les données ressemblent à ceci:
Tout d'abord, MF Wong a noté que vous n'avez pas besoin des cinq premiers enregistrements. Vous pourriez utiliser
Home, Remove Rows, Remove Top Rows… , 5 Rows.
Excel MVP Oz du Soleil d'Excel on Fire s'est également débarrassé de ces cinq éléments, mais il l'a fait quand ils étaient encore des colonnes.
Ensuite, ajoutez une colonne, ajoutez une colonne d'index, à partir de 0. Cela génère une nouvelle colonne de 0 à NN.
Une fois la nouvelle colonne Index sélectionnée, accédez à l'onglet Transformer et choisissez le menu déroulant Standard dans le groupe Onglet Numéro. Attention: il existe une liste déroulante similaire dans l'onglet Ajouter une colonne, mais la sélection de celle de l'onglet Transformer empêche l'ajout d'une colonne supplémentaire. Choisissez Modulo dans cette liste déroulante, puis spécifiez que vous voulez le reste après avoir divisé par 5.
ensuite
Cela génère une série de nombres de 0 à 4 répétés encore et encore.
À partir de là, les étapes pour afficher les noms des employés sont similaires à ma vidéo originale.
Ajoutez une colonne conditionnelle qui apporte le nom ou la valeur Null, puis Fill Down. D'autres méthodes de calcul de cette colonne sont disponibles dans Power Query: Utilisation de clauses Else If dans les colonnes conditionnelles.
Remplissez vers le bas pour remplir le nom de la première ligne aux cinq lignes suivantes.
Merci à MF Wong pour sa vidéo. Assurez-vous d'activer CC pour les sous-titres en anglais.
https://www.youtube.com/watch?v=So1n7sLE_Mg
Vidéo de Peter Bartholomew:
https://www.youtube.com/watch?v=gb3OPfF_BNc
Michael Karpfen s'est également rendu compte qu'il n'était pas nécessaire de supprimer les totaux et de les rajouter plus tard. Son code M est:
let Quelle = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Höher gestufte Header" = Table.PromoteHeaders(Quelle, (PromoteAllScalars=true)), #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",(("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))), #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Geänderter Typ", ("Category Description"), "Attribut", "Wert"), #"Hinzugefügter Index" = Table.AddIndexColumn(#"Entpivotierte andere Spalten", "Index", 1, 1), #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Hinzugefügter Index", "Benutzerdefiniert", each Number.Mod((Index)-1,5)), #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Benutzerdefiniert.1", each if (Benutzerdefiniert)=0 then (Attribut) else null), #"Nach unten gefüllt" = Table.FillDown(#"Hinzugefügte benutzerdefinierte Spalte1",("Benutzerdefiniert.1")), #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Nach unten gefüllt",("Benutzerdefiniert.1", "Attribut", "Category Description", "Wert", "Index", "Benutzerdefiniert")), #"Geänderter Typ1" = Table.TransformColumnTypes(#"Neu angeordnete Spalten",(("Benutzerdefiniert", type text))), #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Geänderter Typ1", "Benutzerdefiniert.2", each if (Benutzerdefiniert) = "0" then "TOTAL" else "Q"&(Benutzerdefiniert)), #"Neu angeordnete Spalten1" = Table.ReorderColumns(#"Hinzugefügte benutzerdefinierte Spalte2",("Attribut", "Category Description", "Benutzerdefiniert.1", "Wert", "Index", "Benutzerdefiniert", "Benutzerdefiniert.2")), #"Entfernte Spalten" = Table.RemoveColumns(#"Neu angeordnete Spalten1",("Attribut", "Index", "Benutzerdefiniert")), #"Pivotierte Spalte" = Table.Pivot(#"Entfernte Spalten", List.Distinct(#"Entfernte Spalten"(Benutzerdefiniert.2)), "Benutzerdefiniert.2", "Wert", List.Sum), #"Neu angeordnete Spalten2" = Table.ReorderColumns(#"Pivotierte Spalte",("Benutzerdefiniert.1", "Category Description", "Q1", "Q2", "Q3", "Q4", "TOTAL")), #"Sortierte Zeilen" = Table.Sort(#"Neu angeordnete Spalten2",(("Benutzerdefiniert.1", Order.Ascending))), #"Umbenannte Spalten" = Table.RenameColumns(#"Sortierte Zeilen",(("Benutzerdefiniert.1", "Employee Name"))) in #"Umbenannte Spalten"Notez que Josh Johnson a également utilisé une colonne Index, mais comme l'une des toutes premières étapes et l'a utilisée comme un tri dans l'une des étapes finales.
Revenez à la page principale du défi Podcast 2316.
Lisez l'article suivant de cette série: Power Query: Extraction de 2 caractères de gauche à partir d'une colonne.
