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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TheBIGuy_PBI
Helper II
Helper II

Trying to merge a column into one row

Hi, I'm trying to take a table and merge the "Answers combined" into one row, grouped by the QuestionText.  It's probably easier than I think but everything I try has not worked.  

Any help on this would be really appreciated.  I've been able to use the ConcatenateX function in PowerQuery, but it just combines everything.  I'm trying to combine them by QuestionText.

A few guys here have helpful, taking me part of the way, but this is the step where I'm stuck

Any help is appreciated.

TheBIGuy_PBI_0-1681304765997.png

Thanks, Steve

1 ACCEPTED SOLUTION
barritown
Super User
Super User

Hi @TheBIGuy_PBI

I guess you need such a measure:

barritown_0-1681331676621.png

Here it is in the text format for convenience:

 

Merged = CONCATENATEX ( data, data[Answer], UNICHAR(10) )

 

The same option works if you want to create a separate table with the merged answers:

Table with Merged Answers = 
SUMMARIZE ( data, data[Question], 
            "Merged Answers", CONCATENATEX ( data, data[Answer], UNICHAR(10) ) )

 

 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

View solution in original post

2 REPLIES 2
barritown
Super User
Super User

Hi @TheBIGuy_PBI

I guess you need such a measure:

barritown_0-1681331676621.png

Here it is in the text format for convenience:

 

Merged = CONCATENATEX ( data, data[Answer], UNICHAR(10) )

 

The same option works if you want to create a separate table with the merged answers:

Table with Merged Answers = 
SUMMARIZE ( data, data[Question], 
            "Merged Answers", CONCATENATEX ( data, data[Answer], UNICHAR(10) ) )

 

 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Holy sh**! This worked perfectly.  I took your suggestion and modified it, but your solution was the perfect template for me to get my project done.

I was able to complete the project with this code and some formatting to get the job done.

Treemap2 = SUMMARIZE (
            'Treemap', Treemap[QUESTIONTEXT],
            "Merged Answers", CONCATENATEX ( Treemap, Treemap[Answers Combined],
            UNICHAR(10) )
            )

Appreciate your help!!!!




Can't thank you enough for your help!!!!!!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.