Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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.

context
unpivot
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    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

    Kind regards,

    Rohit


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

3 Replies

  • rohit_singh's avatar
    rohit_singh
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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

    Kind regards,

    Rohit


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