cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arb1782
Helper III
Helper III

Need help with custom column formula in PowerQuery

Hi

 

I have a table that's built by referencing another table. Within the second table I need to create a custom column ith the following logic

 

If column X = 'xyz', then "yes"

If column Y = 'xyz', then "yes"

Otherwise "no"

 

I wrote the formula in DAX but the custom column function in PowerQuery appears to use a different language. Could anyone please help?

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @arb1782 ,

You could use the following syntax in "Add a custom column" window.

each if [X]="xyz" then "Yes" else if [Y]="xyz" then "Yes" else "No"

9.22.3.1.png

9.22.3.2.PNG

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

View solution in original post

6 REPLIES 6
Eyelyn9
Community Support
Community Support

Hi @arb1782 ,

You could use the following syntax in "Add a custom column" window.

each if [X]="xyz" then "Yes" else if [Y]="xyz" then "Yes" else "No"

9.22.3.1.png

9.22.3.2.PNG

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

View solution in original post

Thanks all

PaulDBrown
Super User II
Super User II

@arb1782 

 

Use the Option in the ribbon to add a new conditional column and write the logic in the interface. You can select different columns.

For example:

PQ Condit Col.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Ah, I didn't spot that before, thanks! I've set it up as is following:

 

If column x CONTAINS 'xyz' then yes

OR if column y is equal to 'xyz' then yes

Otherwise No

 

Mhy remaining problem is that when column x is null, PBI returns error instead of No. Any ideas how to rectify this? There doesn't appear to be 'isnull' in the available criteria.

Any further thoughts on my reply @PaulDBrown 

amitchandak
Super User IV
Super User IV

@arb1782 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Can you see if this can help:https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.