Forum Discussion

Ibad_Khan's avatar
Ibad_Khan
Helper II
3 years ago
Solved

Data Transformation Guidance

Hello PBI Community, hope you all're doing good. I need to tranform the data in such a way that only months from month column transformed in each column of month and show corresponding values against it. Sample is attached below.

Looking forward for some positiove responce as usual.

 

Regards,

Ibad.

 

 

 

Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

     

     

    Hi Ibad_Khan ,

    Please have a try.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZG7CoAwDEV/pXQWNPE9auvo5iYOCh0FEf8f69BHoAHpEug97UmyrnI252FuMchManPt9yMGYevF7KcAW3wXWCDk9sgt88AYgNHlMc7jv7x2+ZLmIyEd+yiXr9j3QwMYAzUFVEKIfNCwQiqZb1khnfTpWB+6AQ/0rBBpwE8UCtZIJY0AWCWyg8kDyCrRHgLxAdsL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [member = _t, Depart = _t, Team = _t, Type = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"member", type text}, {"Depart", type text}, {"Team", type text}, {"Type", type text}, {"Month", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Month", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Month", type text}}, "en-US")[Month]), "Month", "Type")
in
    #"Pivoted Column"

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Ibad_Khan , Have month year on both row and column and

     

    The measure below on values

     

    concatenatex(Table, Table[Type])

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

     

     

    Hi Ibad_Khan ,

    Please have a try.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZG7CoAwDEV/pXQWNPE9auvo5iYOCh0FEf8f69BHoAHpEug97UmyrnI252FuMchManPt9yMGYevF7KcAW3wXWCDk9sgt88AYgNHlMc7jv7x2+ZLmIyEd+yiXr9j3QwMYAzUFVEKIfNCwQiqZb1khnfTpWB+6AQ/0rBBpwE8UCtZIJY0AWCWyg8kDyCrRHgLxAdsL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [member = _t, Depart = _t, Team = _t, Type = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"member", type text}, {"Depart", type text}, {"Team", type text}, {"Type", type text}, {"Month", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Month", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Month", type text}}, "en-US")[Month]), "Month", "Type")
in
    #"Pivoted Column"

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.