Forum Discussion

harshadrokade's avatar
harshadrokade
Icon for Post Partisan rankPost Partisan
3 years ago

unpivot columns changing field Data type

Hi,

 

I have data as below-

 

Student nameYearParameterAParameterBParameterCParameterD
A202122%9910%67
B202134%8929%78

 

Parameter A & C is % fields & Parameter B & D are whole numbers

 

As I want to create a column chart with Subject values provided in slicer, I had to unpivot the data & the new data looks like below-

 

Student nameYearAttributeValue
A2021ParameterA22
A2021ParameterB99
A2021ParameterC10
A2021ParameterD67
B2021ParameterA34
B2021ParameterB89
B2021ParameterC29
B2021ParameterD78

 

Now all values have come under 1 column as 'Value' & so I lost the formatting of the value like percentage & number.

 

Now If I use this value column in column chart, it shows the percentage values like a normal number value however when I used to create column chart with old table coumns (without unpivot), the percentage fields were being shown with % sign in chart as those were kept with data type as %.

 

Now how to handle this case where if someone selects parameter A & C in slicer, the column chart shoudl show their values as % & when other paramters selected, those to be shown as normal number values.

 

Need Help

5 Replies

    • harshadrokade's avatar
      harshadrokade
      Icon for Post Partisan rankPost Partisan
      3 years ago

      amitchandak Thanks Amit. I did unpivot in power query only. The problem is when i do the same, the value format of the different parameters gets chnaged. How to bring it to the original format. May be a measure will help here but unable to get how to do the same.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Using this M code, i have been able to transform the structure to th eone that you see in the image below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDIEUUaqQNLSEkgYGoCYZuZKsTrRSk4IJcYmIHELkBIjSxDT3EIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student name" = _t, Year = _t, ParameterA = _t, ParameterB = _t, ParameterC = _t, ParameterD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student name", type text}, {"Year", Int64.Type}, {"ParameterA", Percentage.Type}, {"ParameterB", Int64.Type}, {"ParameterC", Percentage.Type}, {"ParameterD", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Student name", "Year"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Attribute]="ParameterA" or [Attribute]="ParameterC" then 1 else 2),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-IN")[Custom]), "Custom", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"1", Percentage.Type}})
in
    #"Changed Type1"

    Hope this helps.

    • harshadrokade's avatar
      harshadrokade
      Icon for Post Partisan rankPost Partisan
      3 years ago

      Thanks Ashish_Mathur . My requirement is I want all the values to be coming in single column as I will be using the same column in my column chart on Y axis. If i keep 2 columns, I cant use multiple columns on Y axis. I think I will have to write a measure like if I select a specific paramater in slicer, then the value column will be formatted to % or else it will be shown as number. Then I will use this measure on my Y axis of column chart.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        I think you need to use the new (unreleased) "Field Parameter" feature.