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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
saipawar
Helper IV
Helper IV

How to create word cloud from a column that has multiple values in a single row?

Hi,

Here's  a business question I'd like to answer with my data - 

Q) What topics is my audience the most interested in?

A form is filed out by the attendees from where they pick the topics.

Here's how the data looks - 

Technology Interest
Calling, Video, Messaging, Meetings, Devices, Contact Center
AI, Contact Center, Calling
AI, Messaging, Contact Center, Calling
AI, Contact Center, Calling
AI, Contact Center, Calling
Calling, Video, Messaging, Meetings, Devices, Contact Center
AI, Video, Meetings, Devices

 

I used a delimiter to extract individual values and create a word cloud but the word cloud takes only 1 column at a time and after using a delimiter, I am left with 5 or 6 new columns.

 

Alternatively, I  picked the first column from the delimiter and tried to create a statement that would give dynamic values.

For example - Out of the 7 attendees, 5 picked up AI as their first preference.

 

I wasn't able to get through the "AI " part in the statement. 

 

Any help around this or a different approach to address this business question is really really appreciated.

 

Thanks.

1 ACCEPTED SOLUTION

I threw together a PBIX. Attached.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

You could split the values out in Query Editor and then create a new table like:

 

Table =
  VAR __Column1 = SELECTCOLUMNS('Table',"Words",[Column 1])
  VAR __Column2 = SELECTCOLUMNS('Table',"Words",[Column 2])
  VAR __Column3 = SELECTCOLUMNS('Table',"Words",[Column 3])
  VAR __Column4 = SELECTCOLUMNS('Table',"Words",[Column 4])
  VAR __Column5 = SELECTCOLUMNS('Table',"Words",[Column 5])
  VAR __Column6 = SELECTCOLUMNS('Table',"Words",[Column 6])
  VAR __Column7 = SELECTCOLUMNS('Table',"Words",[Column 7])
RETURN
 UNION(__Column1, __Column2, __Column3, __Column4, __Column5, __Column6, __Column7)

 

Feed [Words] column in this table into your word cloud.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

Thanks for responding so quickly.

 

So here's what I understand - 

a. VAR __Column1 to VAR __Column7 are the columns generated using the delimiter?

b. To create an attribute called Table, I need to "Add New Column " by right clicking on the dataset on the Visualization page

c. Which is [Column 1] in your query? When are these generated?

 

Can you maybe use some dummy names for the tables and columns?

 

Thanks.

 

No, you would use New Table to create a new table in your data model. [Column 1], [Column 2] ... are the columns created when you split out your original column. I am creating variables (__Column1, __Column2...) to hold their contents and then using UNION to append them all together.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Okay.. I think I am getting it. Just a little more help in the beginning of the query.

Looks like I am messing up the syntax when creating a new table 

wordcloud1.PNG

 

Can you please check the syntax for the first two lines? I tried the open and closed braces. 

 

Thanks for being patient with me.

 

Cheers.

I threw together a PBIX. Attached.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

That worked!! Thanks @Greg_Deckler You're awesome😎

Awesome!


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Sure, 

 

Table = 

VAR col1 = ...

 

col1 will be the name of your variable.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.