Forum Discussion
unpivot table with milestones
Hello everybody,
I need help with the following example.
I want the milestones start date --> (1.RH, 2. LH, 3.Best, 4.VA, 5.BÜ, 6.EA) and end date --> (1.RH, 2. LH, 3.Best, 4.VA, 5.BÜ, 6.EA) unpivotieren like example after.
example before
example before
example after
example after
Hi Anonymous
yes, make sure your headers contain characters that make splitting like in the following example possible:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRAmEjIDYGYhOlWB2QOIhvCsRmQGwOxBZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, A_1 = _t, A_2 = _t, B_1 = _t, B_2 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value") in #"Pivoted Column"attaching file as well
7 Replies
- amitchandak
Super User
If the first row is not there and the second row have unique column name, you can do it in Dax Like that
union(
SELECTCOLUMNS(table,table[Project Id], "Milestore" ,"RH1", "Start Date", table[1.RH.start], "END Date", table[1.RH.start]),
SELECTCOLUMNS(table,table[Project Id], "Milestore" ,"2LH", "Start Date", table[1.LH.start], "END Date", table[1.LH.start])
)- amitchandak
But ImkeF can tell us a better solution.
- ImkeF
Community Champion
Hi Anonymous
yes, make sure your headers contain characters that make splitting like in the following example possible:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRAmEjIDYGYhOlWB2QOIhvCsRmQGwOxBZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, A_1 = _t, A_2 = _t, B_1 = _t, B_2 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value") in #"Pivoted Column"attaching file as well
