Forum Discussion
CONVERT SQL IN STATEMENT TO POWER QUERY
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.
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:
Regards,
Jimmy Tao
1 Reply
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:
Regards,
Jimmy Tao