Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

column formatting issues

I am having an issue where rather than showing the ID multiple times i want to show ID once but the Type region and price to all appear on the same line rather than seperate please see example data ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    For this error, it means you have more than one value in same Megerd ID for Type or Region or Price.

    It like this:

     

    So please adjust the Pivot function as below:

     

    here is M code, you could try it.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOzc8vyQAy/PKLwLSCUqwOREoBpygI++SXg8WM8IgF55ci6QaJeiQWpcA1xMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Merged = _t, Type = _t, Region = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Merged", Int64.Type}, {"Type", type text}, {"Region", type text}, {"Price", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"Type", "Region", "Price"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Merged"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Max)
in
    #"Pivoted Column"

     

    Regards,

    Lin

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Anonymous 

 

If you are referring matrix visual layout?

Then you can switch off the stepped layout.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Mariusz  sorry bad explanation i need to do this in Power Bi in M Query. As these columns need to be filters, i have tried pivotting the data then unpivotting but i get an error as says there can't be multiples

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    Can you paste the sample from Excel into the body of this post?

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Mariusz 

      MergedTypeRegionPrice
      1Smooth  
      1 North 
      1  Low
      2  Low
      2 South 
      2Hard  
      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        Hi Anonymous 

         

        Try this 

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOzc8vyQAyFMA4VgciDOL55RdBZZBFQdgnvxwsZoRHLDi/FEk3SNQjsSgFYVEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Merged = _t, Type = _t, Region = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Merged", Int64.Type}, {"Type", type text}, {"Region", type text}, {"Price", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"Type", "Region", "Price"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Merged"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

        or see the attached 

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        LinkedIn