Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

unpivot the data and consider the context

Hi, I want to create a combo visual and so the data needed is one common axis column and two values,

I could't find a way to create visual with the original data, the data seems need to be unpivoted and there are many others fiters to be considered, like location and other columns not listed.

I am new user of DAX and ask for help,  could this problem can be solved use DAX?  Thank you.

1 ACCEPTED SOLUTION
rohit_singh
Solution Sage
Solution Sage

Hi @Anonymous ,

Copy and paste this M-code into a blank query to get the details of each step. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmMgNkeiTZVidaKVnKAiRmARBG0OlnUG6UXWbARXDpJ2AbLMkDQZI5THxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, bar_month1 = _t, bar_month2 = _t, bar_month3 = _t, line_month1 = _t, line_month2 = _t, line_month3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"bar_month1", Int64.Type}, {"bar_month2", Int64.Type}, {"bar_month3", Int64.Type}, {"line_month1", Int64.Type}, {"line_month2", Int64.Type}, {"line_month3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Month"}})
in
    #"Renamed Columns"

 

This will give you the expected result

rohit_singh_0-1655384906495.png

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

View solution in original post

3 REPLIES 3
rohit_singh
Solution Sage
Solution Sage

Hi @Anonymous ,

Copy and paste this M-code into a blank query to get the details of each step. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmMgNkeiTZVidaKVnKAiRmARBG0OlnUG6UXWbARXDpJ2AbLMkDQZI5THxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, bar_month1 = _t, bar_month2 = _t, bar_month3 = _t, line_month1 = _t, line_month2 = _t, line_month3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"bar_month1", Int64.Type}, {"bar_month2", Int64.Type}, {"bar_month3", Int64.Type}, {"line_month1", Int64.Type}, {"line_month2", Int64.Type}, {"line_month3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Month"}})
in
    #"Renamed Columns"

 

This will give you the expected result

rohit_singh_0-1655384906495.png

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

Anonymous
Not applicable

Thank you very much, I will try it, and by the way, if I use DAX create a calculation that use switch, could it be possible to solve that?

Hi @Anonymous ,

You're welcome. It is slightly more complicated in DAX but it can be done. Take a look at these articles :

Unpivot using DAX

https://simplifypowerbi.com/?p=325

 

Pivot using DAX

https://dataap.org/blog/2021/06/25/pivot-your-table-in-dax/

 

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

 

Kind regards,

Rohit

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors