cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KAURM
Helper I
Helper I

Compare one list (column) to another in Power Query Editor to return a true/false if it appears

Morning, I hope you are all well.

 

I'm struggling with two columns in two queries, the first is in the query #"Key phrases from responses":

 

KAURM_0-1638263691355.png

 

and the second is in #"Categories" query:

KAURM_1-1638263777356.png

 

I also have copied these two queries with just the column (list) key phrases in that query to #"Key phrases from responses (2) and #"Categories (2) (#"Sheet (2) in the screenshot as renamed after.

 

I want to check if the row values (words/phrases) in key phrases column in the Key phrases from responses query appear or do not appear in the Categories [key phrases] column (list) by returning a true or false value in an additional custom column. 

 

I also have the data in an excel file please message me if you would like me to send it to you to help with the query as I am unable to attach a file or copy and paste due to exceeding the character limit. 

 

Any assistance you can provide in this matter would be much appreciated. 

 

Thanking you.

 

Kind Regards,

 

KAURM

 

@BA_Pete  @AlexisOlson  @mahoneypat  @Vera_33 @v-stephen-msft @ImkeF @edhans @Eyelyn9 @v-kelly-msft 

 

 

2 ACCEPTED SOLUTIONS
Vera_33
Super User
Super User

Hi @KAURM 

 

It looks like they are all lower case already, you can go directly to the query #"Key phrases from responses" to add a custom column

 Table.AddColumn(yourPreStep, "yourNewColumnName", each List.Contains(Categories[key phrases],[key phrases]))

View solution in original post

KAURM
Helper I
Helper I

Hi @Vera_33 

 

Thank you for your help! I did this before and it didn't work.

 

Just realised that when using Power BI Text Analytics the key phrases/words have spaces in front of them.

 

Just trimmed the column and now it works 🙂

 

Thanks for your help!

View solution in original post

2 REPLIES 2
KAURM
Helper I
Helper I

Hi @Vera_33 

 

Thank you for your help! I did this before and it didn't work.

 

Just realised that when using Power BI Text Analytics the key phrases/words have spaces in front of them.

 

Just trimmed the column and now it works 🙂

 

Thanks for your help!

View solution in original post

Vera_33
Super User
Super User

Hi @KAURM 

 

It looks like they are all lower case already, you can go directly to the query #"Key phrases from responses" to add a custom column

 Table.AddColumn(yourPreStep, "yourNewColumnName", each List.Contains(Categories[key phrases],[key phrases]))

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors