Unable to create report in Power Bi

[dodiyal]

Hello Team,

I have one data set which i wish to convert into below mentioned table, can you help.

Regional Support Location	Customer Service Experience
Turkey, Middle East, Africa, Southern Asia	Unacceptable
Latin America	Unacceptable
Asia Pacific	Unacceptable
Latin America	Below Average
Latin America	Average
Latin America	Average
China	Below Average
Europe	Unacceptable
Latin America	Above Average
Europe	Below Average
Europe	Good

The desired outuput table is

Mode (Percent)	Unacceptable	Below Average	Average	Above Average	Good
Customer Service Experience	0.4	0.3	0.2	0.1	0.1

[v-kkf-msft · Accepted solution]

Hi @dodiyal ,

Please try the following code in Power Query Editor: 

let
    Source = Table.FromValue(#"Table"),
    #"Grouped Rows" = Table.Group(Source, {"Customer Service Experience"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count] / List.Sum(#"Grouped Rows"[Count])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Percentage.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Mode (Percent)", each "Customer Service Experience"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[#"Customer Service Experience"]), "Customer Service Experience", "Custom", List.Sum)
in
    #"Pivoted Column"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.