Transforming data table

[Anonymous]

Hi,

In PowerBI i have a data Table 1, which needs to be converted a in Table 2.

Is it possible? Thanks in advance for your help.

Table 1:
Table 1 (from)

Table 2:
Table 2 (to)

BR,

Anabn

[slorin · Accepted solution]

Hi,

let
Source = Your_Source,
Unpivot = Table.UnpivotOtherColumns(Source, {"ID", "Activity"}, "Attribute", "Value"),
Combine_Columns = Table.CombineColumns(Unpivot,{"Activity", "Attribute"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Attribute.1"),
Pivot = Table.Pivot(Combine_Columns, List.Distinct(Combine_Columns[Attribute.1]), "Attribute.1", "Value", 
    each Text.Combine(List.Transform(_, Text.From), " - "))
in
Pivot

Stéphane

[wdx223_Daniel]

=Table.Combine(Table.Group(PreviousStepName,"ID",{"n",each let a=List.Skip(Table.ColumnNames(_),2) in #table({"ID"}&List.TransformMany([Activity],each a,(x,y)=>x&"_"&y),{List.Combine(Table.Rows(Table.SelectColumns(_,a)))})})[n])

[slorin]

Hi,

let
Source = Your_Source,
Unpivot = Table.UnpivotOtherColumns(Source, {"ID", "Activity"}, "Attribute", "Value"),
Combine_Columns = Table.CombineColumns(Unpivot,{"Activity", "Attribute"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Attribute.1"),
Pivot = Table.Pivot(Combine_Columns, List.Distinct(Combine_Columns[Attribute.1]), "Attribute.1", "Value")
in
Pivot

Stéphane 

[Anonymous]

Hi, Thank you 🙂
this worked perfectly fine.
But some values have Error after Pivot, even though it had a proper date format before pivot.
Error

[ppm1]

You could do it by first unpivoting the Date 1, Date 2, and Date 3 columns, create a new column that merges the first column with the new Attribute column (Date 1/2/3), and then pivoting on that new column. However, you should consider just doing the unpivot step only to set your data up for easier analysis (you could always create your design table in a visual as needed).

Pat