Converting Columns to Rows

[ahjamil]

Hi, 
I need help converting columns to a single column. I have following Table:

Group	Jan	Feb	Mar
G1	10	10	9
G2	11	14	11
G3	10	12	13
G4	12	11	12
G5	11	9	10

and I need to create a new table like the following:

Group	Month	Value
G1	Jan	10
G2	Jan	11
G3	Jan	10
G4	Jan	12
G5	Jan	11
G1	Feb	10
G2	Feb	14
G3	Feb	12
G4	Feb	11
G5	Feb	9
G1	Mar	9
G2	Mar	11
G3	Mar	13
G4	Mar	12
G5	Mar	10

I need this to create a WaterFall Chart that can provide user a selection of months. Please help with a solution.
Thanks in anticipation.

Ahmad

[tackytechtom · Accepted solution]

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/

[Jihwan_Kim · Accepted solution]

Hi,

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

[tackytechtom · Accepted solution]

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/

[ahjamil]

This was extremely helpful Tom.
Thank you. 

[Jihwan_Kim · Accepted solution]

Hi,

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

[tackytechtom · Accepted solution]

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/

[ahjamil]

Hi Tom, 
Thank you for explaining the Power Query solution as well. I have a question that I get additional data in this table monthly, so would the Power Query table refresh data whenever I will refresh the report?