Converting Columns to Rows

Hi ahjamil ,

Power Query is a fantastic tool to unpivot columns and solve this issue:

1) You first need to create a duplicate of your table. Right click on the table in the query list on the left and click duplicate.

2) Right click on the Group column and choose unpviot other columns:

3) Double click on the name of the column (Attribute) and rename it to Month:

Here the whole M code that you can use in the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjdU0lEyNIARlkqxOkBBIxAfLGMCYYFFjeFKwfLGEFETuIAhhAUWNYUJWEI0xcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Jan = _t, Feb = _t, Mar = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Group"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Month"}})
in
    #"Renamed Columns"

Does this help? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Hi,

If you can go into Power Query Editor, please select a Group Column, and click Transform tab -> Unpivot Other Columns button.

Hi ahjamil ,

Since you asked here in the DAX Commands and Tips forum, here a possible solution in DAX. Note, if you have the possibility to do it in Power Query, I'd recommend to do it there since the DAX code is not very sunstainable. For instance, next time a new month is added to your table (i.e. April) you need to rewrite the code...

DAX:

Table = 
UNION ( 
    SELECTCOLUMNS(Table, "Group", Table[Group], "Month", "Jan", "Value", Table[Jan] ),
    SELECTCOLUMNS(Table, "Group", Table[Group], "Month", "Feb", "Value", Table[Feb] ),
    SELECTCOLUMNS(Table, "Group", Table[Group], "Month", "Mar", "Value", Table[Mar] )
)

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/