cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jackman366
Regular Visitor

Translate SQL function into Power Query

Hi community, 

We used the following SQL statement to bring a column from the table TBL1211 into TBL2211 through the table TBL1201.

SELECT
DATA.TBL2211.SDMCU AS "CC (MCU)",

( SELECT DATA.TBL1211.KAVL
FROM DATA.TBL1211
INNER JOIN
DATA.TBL1201 ON DATA.TBL1211.KAGID = DATA.TBL1201.CMGID
WHERE DATA.TBL1211.KAPEL = '60'
AND DATA.TBL1201.CMDC = DATA.TBL2211.SDDC
AND DATA.TBL1201.CMDT = DATA.TBL2211.SDDT
AND DATA.TBL1201.CMLN = DATA.TBL2211.SDLN
AND ROWNUM <= 1
) AS "(FLTR 60)"

FROM
DATA.TBL2211

 Now we decided to Extract each table alone and then do the merges in Power Query. I did the merging, but i have a problem. Some elements are duplicates (Same columns we used for join : SDDC, SDDT and SDLN, but they have 2 KAGID), now we fixed this issue when we used SQL by using the function ROWNUM. So, i need an equivalent of this function in Power Query. 

Thank you. 

1 REPLY 1
lbendlin
Super User
Super User

The safest way to do this in Power Query is to use "Group By" or to have an index column or both.

 

Word of warning: Merge operations in Power Query are expensive.  It would be preferable to continue to do these in the data source.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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

Top Solution Authors
Top Kudoed Authors