Forum Discussion

stephenstill's avatar
stephenstill
New Member
3 years ago

Row_Number() works in SMSS, but creates two rows when the data comes into Power BI

I use the following in smss: Select Row_Number() over(partition by .Location,SKU order by Location,SKU) as RN. In SMSS I get one row per SKU by location,   but when I use the exact same query in P...
Need Help
stephenstill's avatar
stephenstill
New Member
3 years ago

That is why I wonder if there is a bug-I copied and pasted my SMSS query into the get data from azure query box, and as soon as it loads, I have two rows in the data table in Power BI where I only had one row in SMSS.

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

     

    I'm not convinced it's a bug to be honest but, unfortunately, it's impossible for me to recreate your scenario without access to your data source(s) etc. which obviously isn't possible.

    That being said, I'm wondering whether you have trailing/leading whitespace on your values that SSMS ignores in the output, but that Power Query wouldn't ignore. Maybe try using LTRIM(RTRIM(...)) around [Location] and [SKU] in your SQL? It's probably a long-shot, but worth a try.

     

    If you're sure it's not something locally that's causing the issue then you can raise a support ticket with MS :

    https://powerbi.microsoft.com/en-us/support/free/ 

     

    Pete