J'ai un fichier CSV contenant des informations sur certains lots de production. Lorsqu'il est chargé dans l'éditeur Excels PowerQuery, le tableau ressemble à ceci:
Batch Date RawMaterials Amounts
123 01.01.2020 Fe 70
Cr 19
Ni 11
234 01.02.2020 Fe 72
Cr 17
Ni 11
Pour rendre ce tableau plus lisible, je cherche un moyen de le transposer juste partiellement pour le transformer en un format comme ceci:
Batch Date RawMaterial1 RawMaterial2 RawMaterial3 Amount1 Amount2 Amount3 123 01.01.2020 Fe Cr Ni 70 19 11 234 01.02.2020 Fe Cr Ni 72 17 9
Y a-t-il un moyen de réaliser cela avec PowerQueryM seul?
3 Réponses :
Cela semble fonctionner
Dépivote toutes les colonnes sauf les deux premières
Dupliquer la colonne de données
Changer le type de colonne en nombre pour forcer une erreur sur les colonnes non numériques
Transformez toutes les erreurs en quelque chose de reconnaissable, comme 999999999999
Filtrez en fonction de cela en deux tables, et ajoutez un index à chaque table
Fusionnez les deux tables ensemble
Ajouter une nouvelle colonne, en utilisant l'index pour voir si Batch est identique à la ligne précédente pour éliminer les doublons
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date", "Batch"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Value", "Value - Copy"),
#"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Value - Copy", type number}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Value - Copy", 999999999999999}}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([#"Value - Copy"] = 999999999999999)),
#"Filtered Rows2" = Table.SelectRows(#"Replaced Errors", each ([#"Value - Copy"] <> 999999999999999)),
Index1 = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
Index2 = Table.AddIndexColumn(#"Filtered Rows2", "Index", 0, 1),
#"Merged Queries" = Table.NestedJoin(Index2,{"Index"},Index1,{"Index"},"Index3",JoinKind.LeftOuter),
#"Expanded Index3" = Table.ExpandTableColumn(#"Merged Queries", "Index3", {"Value"}, {"Value.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Index3",{"Attribute", "Value - Copy"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Batch.1", each if [Index] = 0 then [Batch] else if #"Removed Columns"{[Index]-1}[Batch] = [Batch] then null else [Batch]),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Date.1", each if [Index] = 0 then [Date] else if #"Removed Columns"{[Index]-1}[Batch] = [Batch] then null else [Date]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Batch", "Date", "Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Batch.1", "Date.1", "Value.1", "Value"})
in #"Reordered Columns"
Super boulot! Cela marche. En prime, j'aimerais savoir si vous pouvez changer les textes en double en une chaîne vide (s'ils sont dans des lignes consécutives, voir la question initiale). Cela améliorerait à nouveau la lisibilité du tableau.
Tout d'abord, un grand merci à @horseyride. J'ai beaucoup appris de votre code suggéré. Malheureusement, lorsque j'ai essayé d'ajouter la colonne de date à la zone de pivotement, j'ai trouvé une petite faille dans le code. Mais grâce aux leçons que j'en ai tirées, j'ai pu produire une version légèrement plus générique qui suit essentiellement le même algorithme.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Unpivot Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Batch"}, "Attribut", "Wert"),
Filter1 = Table.SelectRows(#"Unpivot Columns", each ([Attribut] <> "Amount1" and [Attribut] <> "Amount2" and [Attribut] <> "Amount3")),
ModFilter1 = Table.AddColumn(Filter1, "Benutzerdefiniert", each if [Attribut] = "Date" then [Attribut] else [Wert], type text),
Filter2 = Table.SelectRows(#"Unpivot Columns", each ([Attribut] <> "RawMaterial1" and [Attribut] <> "RawMaterial2" and [Attribut] <> "RawMaterial3")),
#"IndexFilter1" = Table.AddIndexColumn(ModFilter1, "Index", 0, 1),
#"IndexFilter2" = Table.AddIndexColumn(Filter2, "Index", 0, 1),
#"Join Filtered Indexes" = Table.NestedJoin(IndexFilter1,{"Index"},IndexFilter2,{"Index"},"IndexFilter2",JoinKind.LeftOuter),
#"Expand Joined Column" = Table.ExpandTableColumn(#"Join Filtered Indexes", "IndexFilter2", {"Wert"}, {"IndexFilter2.Wert"}),
#"Remove Columns" = Table.RemoveColumns(#"Expand Joined Column",{"Index", "Attribut", "Wert"}),
#"Rename Columns" = Table.RenameColumns(#"Remove Columns",{{"Benutzerdefiniert", "Attribut"}, {"IndexFilter2.Wert", "Wert"}})
in
#"Rename Columns"
Je garde la réponse de horseyrides cochée comme la bonne réponse pendant qu'il résout mon question initiale telle qu'elle était.
Cela peut être fait un peu plus simplement:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Batch", Int64.Type}, {"Date", type date}, {"RawMaterial1", type text}, {"RawMaterial2", type text}, {"RawMaterial3", type text}, {"Amount1", Int64.Type}, {"Amount2", Int64.Type}, {"Amount3", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Batch", "Date"}, "Attribute", "Value"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Type", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Type]), "Type", "Value")
in
#"Pivoted Column"