Converting Columns in Dataset to Categories to use in Matrix

[lotus22]

I need to present the data in following format using Matrix or any visual

However, the data is currently available in Columns. I do not have categories defined yet. I was thinking of using Unpivot Columns but that may not work as the data is combination of text,  numbers, and percentages. Looking for feedback for how I can take data below to format as above?

Tables

Safety	Recordable Incidences
Lost Time Incidences
Safety Comments
Quality	Customer Concerns
Customer Concerns Details
Warranty Calls
Warranty Calls Details
Liaison Calls
Liaison Calls Details
Cost of Quality

Recordable Incidences

Lost Time Incidences

Safety Comments

Customer Concerns

Customer Concerns Details

Warranty Calls

Warranty Calls Details

Liaison Calls

Liaison Calls Details

Cost of Quality

[v-yingjl · Accepted solution]

Hi @lotus22 ,

You can try this query and use a matrix to show the hierarchy in Power BI:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLQA5GOQOwExM5AbAzEpkBsBsSWIL6pqlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Recordable Incidences" = _t, #"Lost Time Incidences" = _t, #"Safety Comments" = _t, #"Customer Concerns" = _t, #"Customer Concerns Details" = _t, #"Warranty Calls" = _t, #"Warranty Calls Details" = _t, #"Liaison Calls" = _t, #"Liaison Calls Details" = _t, #"Cost of Quality" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Recordable Incidences", Int64.Type}, {"Lost Time Incidences", type number}, {"Safety Comments", type text}, {"Customer Concerns", type text}, {"Customer Concerns Details", type text}, {"Warranty Calls", Int64.Type}, {"Warranty Calls Details", Int64.Type}, {"Liaison Calls", Int64.Type}, {"Liaison Calls Details", Int64.Type}, {"Cost of Quality", Percentage.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Category", each if [Index] <= 3 then "Safety" else "Quality",type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.