CONVERT SQL IN STATEMENT TO POWER QUERY

[Anonymous]

Hello everyone,

Because I am using OData as the data source, I cannot query data as I want when inputting to data model.

So I need to use Power Query to modify my table.

For example as the picture, there are table A and table B. I want to create a column in table A named ID_Name. If the value in column Name of table A exists in table B, the value in ID_Name column will be Name column only. Otherwise, it will be ID+Name.

Here is the example SQL statement:

SELECT CASE WHEN A.NAME IN (SELECT NAME FROM B)

             THEN A.ID

             ELSE A.ID+A.NAME END AS ID_NAME
FROM A

How can I convert this SQL statement into Power Query?

Thank you for your help.

[v-yuta-msft · Accepted solution]

Hi khoahong,

To achieve your requirement, please refer to following steps.

1.Merge table ‘A’ and table ‘B’ with left outer join.

2.Then create a custom column using M code like below:

= Table.AddColumn(#"Expanded B", "New Column", each if [B.NAME] = null then [ID] & [NAME] else [ID])

The result is like below and you can refer to PBIX here:

https://www.dropbox.com/s/zgyzfucv6ddz3vk/CONVERT%20SQL%20IN%20STATEMENT%20TO%20POWER%20QUERY.pbix?d...

 

Regards,

Jimmy Tao