Forum Discussion

Jackman366's avatar
Jackman366
Regular Visitor
5 years ago

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. 

Need Help

1 Reply

Replies have been turned off for this discussion
  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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.