Remove empty cells in the row

[Kpham]

Dataset:

Person	Car Preference
a	Audi
a	BMW
a	Mercedes
b	Toyota
b	Nissan
c	Mercedes
c	KIA
c	VW

Desired Power BI  visual:

Person	Car1	Car2	Car3	Car4
a	Audi	BMW	Mercedes	VW
b	Toyota	Nissan	BMW
c	Mercedes	KIA	VW

How can I achieve this?

[v-eqin-msft]

Hi @Kpham ,

I am not sure if there is a more efficient way...

My approach is as follows:

1. Sort the table by grouping by Person column

rank =
VAR a = [Person]
VAR t1 =
    FILTER ( ALL ( 'Table' ), 'Table'[Person] = a )
RETURN
    RANKX ( t1, [Car Preference],, DESC, SKIP )

2. Create a new Table with distinct person 

Table 2 =
DISTINCT ( SELECTCOLUMNS ( 'Table', "Person", [Person] ) )

3. Add columns separately by rank column. (Take the Car1 column as an example)

Car1 =
CALCULATE (
    MAX ( 'Table'[Car Preference] ),
    FILTER ( 'Table', 'Table'[Person] = 'Table 2'[Person] && 'Table'[rank] = 1 )
)

My final output looks like this:

Please take a look at my pbix file here.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[FrankAT]

Hi @Kpham 

with your dataset I get the following solution:

Cars = CONCATENATEX(VALUES('Table'[Car Preference]),'Table'[Car Preference],", ")

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[ImkeF · Accepted solution]

Hi @Kpham ,

if you want them in separate column, you have to add an index column for the column number to your table: 

https://www.youtube.com/watch?v=-3KFZaYImEY 

[amitchandak]

@Kpham , I think this needs to be done in M. Let us check with the experts

@ImkeF , can you help with this

[Kpham]

Hi,

Thank you for you time. I just reviewed my post and edited the input dataset