Forum Discussion
Unpivot many columns
- 5 years ago
OK, got the data.
Here's the Advanced Editor code: (for these types of problems, I do one Unpivot -> Split the attribute column to get column names -> Pivot)
let Source = Excel.Workbook(File.Contents("J:\data\powerbiForum\UntitledUnp.xls"), null, true), Sheet2 = Source{[Name="Sheet1"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet2, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"ID", Int64.Type}, {"Article_1", type text}, {"Article_2", type text}, {"Article_3", type text}, {"Article_4", type text}, {"Amount_1", Int64.Type}, {"Amount_2", Int64.Type}, {"Amount_3", Int64.Type}, {"Amount_4", Int64.Type}, {"Price_1", Currency.Type}, {"Price_2", Currency.Type}, {"Price_3", Currency.Type}, {"Price_4", Currency.Type}, {"Minutes_1", Int64.Type}, {"Minutes_2", Int64.Type}, {"Minutes_3", Int64.Type}, {"Minutes_4", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)), #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," 0","",Replacer.ReplaceText,{"Date"}), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date", "ID"}, "Attribute", "Value"), #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "NULL")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value"), #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Descending}, {"Article", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute.2"}) in #"Removed Columns"The column names and types might be different at your side.
Good luck
Jensej , You need multiple unpivot
https://kohera.be/blog/power-bi/how-to-unpivot-twice/
or New Table in DAX like
New Table =
Union(
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_1], Table[Price_1], Table[MINUTES_1]),
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_2], Table[Price_2], Table[MINUTES_2]),
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_3], Table[Price_3], Table[MINUTES_3]),
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_4], Table[Price_4], Table[MINUTES_4])
)
- Jensej5 years agoHelper V
Tried this multiple pivot but it doesn't work.
I selected all articles,amounts,price,minutes and then unpivot. First i got a error message and had to change type using locale on all columns and after that it worked.
After that i have two columns Attribute with (article_1,amount_1,price_1 and so on) Value with (7601,7601-A,3,1,45 and so on.
Then i split Attribute column by position right 2 to get rid of all the _1, _2 and so on. I also deleted that column after.
Then i had a Attribute column with article,price,amount,minutes and a column with values. I marked both and then choose pivot on value table but the end results is wrong.. .