Forum Discussion
Pivoting a table with custom values?
Hi there,
I am working on a visual in Power BI that looks something like this:
| ID | Value |
| 1 | pizza |
| 2 | hot dogs |
| 2 | fries |
| 3 | fries |
| 4 | burgers |
| 4 | hot dogs |
| 4 | pizza |
| 5 | burgers |
| 5 | fries |
| 6 | hot dogs |
| 6 | burgers |
| 7 | pizza |
The goal my client has is to not only filter the table so that only IDs with more than one value are featured, but to also move the values to go side-by-side like so:
| ID | Value1 | Value2 | Value3 |
| 2 | hot dogs | fries | |
| 4 | burgers | hot dogs | pizza |
| 5 | burgers | fries | |
| 6 | hot dogs | burgers |
I think I would have to do a pivot of some sort to get this to work, but I have no idea how I could assign Value1, Value2, Value3, and so on dynamically in order to make this possible (i.e. make sure it doesn't break with other data variations, if there are more than 3 values for a single ID, and so on). Is this even feasible in Power BI as it is right now?
It's doable without any pivots:
Ids with More Than One Value = IF( COUNTROWS(VALUES('Table'[Value])) > 1 && HASONEVALUE('Table'[ID]), //i.e more than one value per id and is not the total row CONCATENATEX('Table', 'Table'[Value], ", ") )Hi vicky_ ,thanks for the quick reply, I'll add further.
Hi Jabbornon ,
The Table data is shown below:
Please follow these steps:
1.Use the following DAX expression to create a table
Table 2 = SUMMARIZE('Table','Table'[ID],"Col",CONCATENATEX('Table',[Value],","))2.Copying table data to Excel
3.Use Power BI Desktop to connect to the Excel file where the data is stored and open Power Query.
4.split column
5.Final output
Split columns by delimiter - Power Query | Microsoft Learn
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"All", each Table.AddIndexColumn(_,"Index",1,1)}, {"Count", each Table.RowCount(_), Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] > 1), #"Expanded All" = Table.ExpandTableColumn(#"Filtered Rows", "All", {"Value", "Index"}, {"Value", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded All",{"Count"}), #"Added Prefix" = Table.TransformColumns(#"Removed Columns", {{"Index", each "Value " & Text.From(_, "en-IN"), type text}}), #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "Value") in #"Pivoted Column"Hope this helps.
5 Replies
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"All", each Table.AddIndexColumn(_,"Index",1,1)}, {"Count", each Table.RowCount(_), Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] > 1), #"Expanded All" = Table.ExpandTableColumn(#"Filtered Rows", "All", {"Value", "Index"}, {"Value", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded All",{"Count"}), #"Added Prefix" = Table.TransformColumns(#"Removed Columns", {{"Index", each "Value " & Text.From(_, "en-IN"), type text}}), #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "Value") in #"Pivoted Column"Hope this helps.
It's doable without any pivots:
Ids with More Than One Value = IF( COUNTROWS(VALUES('Table'[Value])) > 1 && HASONEVALUE('Table'[ID]), //i.e more than one value per id and is not the total row CONCATENATEX('Table', 'Table'[Value], ", ") )I have done that in a prior project, but I was wondering specifically if it is possible to separate them into different columns instead of concatenating them into one single column.
If you need a dynamic amount of columns, then no I can't think of a way to get it to show.
If you only have a set number of columns (i.e 3), then you can use TOPN or INDEX in 3 different measures to grab the 1st, 2nd, 3rd value (sorted alphabetically or something). The only downside to this being that there will be blank values / columns and it is tedious to set up and to maintain so I would not recommend.
Hi vicky_ ,thanks for the quick reply, I'll add further.
Hi Jabbornon ,
The Table data is shown below:
Please follow these steps:
1.Use the following DAX expression to create a table
Table 2 = SUMMARIZE('Table','Table'[ID],"Col",CONCATENATEX('Table',[Value],","))2.Copying table data to Excel
3.Use Power BI Desktop to connect to the Excel file where the data is stored and open Power Query.
4.split column
5.Final output
Split columns by delimiter - Power Query | Microsoft Learn
Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
