cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jackman366
New Member

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 III
Super User III

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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors