unpivot columns changing field Data type

[harshadrokade]

Hi,

I have data as below-

Student name	Year	ParameterA	ParameterB	ParameterC	ParameterD
A	2021	22%	99	10%	67
B	2021	34%	89	29%	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 name	Year	Attribute	Value
A	2021	ParameterA	22
A	2021	ParameterB	99
A	2021	ParameterC	10
A	2021	ParameterD	67
B	2021	ParameterA	34
B	2021	ParameterB	89
B	2021	ParameterC	29
B	2021	ParameterD	78

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.

[Ashish_Mathur]

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]

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]

Hi,

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

[amitchandak]

@harshadrokade , Unpivot the table in Power query

https://radacad.com/pivot-and-unpivot-with-power-bi

[harshadrokade]

@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.