Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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:
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |