Need help with custom column formula in PowerQuery

[arb1782]

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?

[v-eqin-msft · Accepted solution]

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"

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

[arb1782]

Thanks all

[PaulDBrown]

@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:

[arb1782]

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.

[arb1782]

Any further thoughts on my reply @PaulDBrown 

[amitchandak]

@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/