Forum Discussion

Swathi1's avatar
Swathi1
Icon for Helper I rankHelper I
1 year ago
Solved

To update columns names dynamically in the grid level.

Hello,

 

I need a help regarding, I have table with the following columns,

Region, Territory, CQM1 Month (Jan), CQM2 Month(February), CQM3 Month (March), PQM1 Month(Oct), PQM2 Month (November),PQM3 Month(December).This table contains aggregated data but here I need to diplay dynamically to update month headers in the grid visual. Expected ouput like Region, Territory, Jan, February, March, Oct, November, December as column headers based on the future months.

Need Help
  • parry2k's avatar
    parry2k
    1 year ago

    Swathi1 the potential solution is to unpivot your table and extract the month, paste the following M query in the advanced editor, and apply the same steps on your data.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClLSUQoBYkMgNgJiYyA2AWJTIDZTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Territory = _t, #"CQM1 Month (Jan)" = _t, #"CQM2 Month(February)" = _t, #"CQM3 Month (March)" = _t, #"PQM1 Month(Oct)" = _t, #"PQM2 Month (November)" = _t, #"PQM3 Month(December)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Territory", type text}, {"CQM1 Month (Jan)", type text}, {"CQM2 Month(February)", type text}, {"CQM3 Month (March)", type text}, {"PQM1 Month(Oct)", type text}, {"PQM2 Month (November)", type text}, {"PQM3 Month(December)", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Region", "Territory"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Month", each Text.BetweenDelimiters([Attribute],"(",")"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"})
in
    #"Removed Columns"

    To visualize, use matrix visual, use month on the columns, and value on the values section of the matrix visual.

7 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    1 year ago

    Swathi1 the potential solution is to unpivot your table and extract the month, paste the following M query in the advanced editor, and apply the same steps on your data.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClLSUQoBYkMgNgJiYyA2AWJTIDZTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Territory = _t, #"CQM1 Month (Jan)" = _t, #"CQM2 Month(February)" = _t, #"CQM3 Month (March)" = _t, #"PQM1 Month(Oct)" = _t, #"PQM2 Month (November)" = _t, #"PQM3 Month(December)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Territory", type text}, {"CQM1 Month (Jan)", type text}, {"CQM2 Month(February)", type text}, {"CQM3 Month (March)", type text}, {"PQM1 Month(Oct)", type text}, {"PQM2 Month (November)", type text}, {"PQM3 Month(December)", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Region", "Territory"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Month", each Text.BetweenDelimiters([Attribute],"(",")"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"})
in
    #"Removed Columns"

    To visualize, use matrix visual, use month on the columns, and value on the values section of the matrix visual.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Swathi1,

    Thanks for reaching out to the Microsoft fabric community forum.

    It looks like you want to dynamically update column headers bsaed on the current month. As parry2k already responded to your query, please go through the response and mark it as solution if your issue is resolved.

     

    I would also take a moment to thank parry2k, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.
    Community Support Team

     

    If this post helps then please mark it as a solution, so that other members find it more quickly.

    Thank you.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Swathi1,

        Can you please confirm if the original issue is where you are struck or where you able to work on the response provided by parry2k and got struck somewhere in between.

         

        If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

        Best Regards,
        Hammad.
        Community Support Team