Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akropp94
Regular Visitor

row duplication when expanding a table column

In query when expanding a table column, rows duplicate like this:

 

Capture1.PNG

 

 

 

 

 

 

 

 

 

becomes


Capture.PNG

I understand why this happens, but is there any way to instead of duplicating the rows, have it so that it does this instead: 

 

 Capture.PNG

 

any sort of solution that does this would be appreciated - including using DAX or something. (It doesnt have to be a comma, just did that for the example)

My data source is sharepoint online - Column 2 source is a choice column which allows multiple values, hence the table. for each row.

 

Regards

Ariel Kropp

1 ACCEPTED SOLUTION
radpir
Resolver II
Resolver II

Hi,

 

Try adding a column in Power Query before expanding the Column 2:

= Text.Combine(Table.Column([Column 2], "Fruit"), ", ")

where "Fruit" is the name of the column in the table that is concatenated and needs to be changed to match your data setup.

 

Hope this helps,

radpir

 

View solution in original post

4 REPLIES 4
radpir
Resolver II
Resolver II

Hi,

 

Try adding a column in Power Query before expanding the Column 2:

= Text.Combine(Table.Column([Column 2], "Fruit"), ", ")

where "Fruit" is the name of the column in the table that is concatenated and needs to be changed to match your data setup.

 

Hope this helps,

radpir

 

worked brilliantly.

thank you

Greg_Deckler
Super User
Super User

Create a measure in DAX like:

 

Measure = CONCATENATEX(VALUES(Apples[Column2]),[Column2],",")

Create a table visualization of Column1 and this measure.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

This did not work - when creating a measure it did not allow this expression, many errors.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.