Pivoting a table with custom values?

[Jabbornon]

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?

[vicky_ · Accepted solution]

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], ", ")
)

[Anonymous · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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.

[Anonymous · Accepted solution]

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.

[vicky_ · Accepted solution]

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], ", ")
)

[Jabbornon]

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. 

[vicky_]

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.