Table Visual With Columns Based On Variable Number Of Categories

[Anonymous]

I am currently trying to create a table visual using a data set that starts in the following format after I query it:

Category_1	Category_2	Value
Str_1.1	Str_2.1	#
Str_1.1	Str_2.1	#
...	...	...
Str_1.1	Str_2.2	#
Str_1.1	Str_2.2	#
...	...	...
Str_1.2	Str_2.1	#
Str_1.2	Str_2.1	#
...	...	...
Str_1.2	Str_2.2	#
Str_1.2	Str_2.2	#
...	...	...

The columns Category_1 and Category_2 each contain names for categories of data that the value listed is classified under.  There can be an arbitrary number of unique category names in each of the two columns.  All possible combinations of category names between the two columns will have the same number of values associated with them.  I want to take this data and use it to make a Table with the following format:

Str_1.1+Str_2.1	Str_1.1+Str_2.2	Str_1.2+Str_2.1	Str_1.2+Str_2.2
#	#	#	#
#	#	#	#
...	...	...	...

I know how I would go about doing this with a fixed number of category names in each category column.  However, I am not sure how to dynamically make this table using Power BI with a variable number of possible names in each column.

I would prefer if the solution to this didn't involve changing the query itself since I am currently working in a project where the rest of the visuals are based around the data being in that format.  However, if this isn't possible to do without modifying the query, I can work with that still.

[MFelix · Accepted solution]

Hi @Anonymous ,

Don't understand what you mean by having a new table, if you are considering adding a new table to your model in this format or if you want to have a table visual with this format in either options you need to add a column with the following code:

[Category1] &" + " &[Category2]

Do a group by the created column with ALL rows in operation

Add a column with the following code: Table.AddIndexColumn([Data], "Index", 1, 1)

Now expand the columns category 1, category 2 and value.

Delete the Data column.

If you want to have it on a visual the use this new column in the columns and the index on the rows.

If you are creating a new table in the query editor you need to do the following steps:

• Reference the original table
• Remove the Category 1 and Category 2 columns
• Select the Concatened and index column and pivot the values

Check the code below for both tables and PBIX file attach:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg4JijfUM1TSAbOMwCxDAwOlWB3sckY45Izw6MOUM8JjphEetxihmIlLH9y+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category1 = _t, Category2 = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category1", type text}, {"Category2", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Category1] &" + " &[Category2], type text),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Custom", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Custom"}, {{"Data", each _, type table [Category1=text, Category2=text, Value=number, Custom=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Category1", "Category2", "Value", "Index"}, {"Category1", "Category2", "Value", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"Data"})
in
    #"Removed Columns"







let
    Source = Table,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Category1", "Category2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value", List.Sum)
in
    #"Pivoted Column"

Regards,

MFelix