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

[stephenstill]

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 Power BI (import data) then for some items only in some locations, it creates a second row for the same sku and location combination when some of the other columns have different values. Is this a bug? IS there a way to merge the two rows when they have the same location and sku?

[stephenstill]

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]

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

[BA_Pete]

Hi @stephenstill ,

Highly unlikely this is a bug in PQ.

If you use the native query method of importing data, PQ just shows the data that the server returns, exactly the same as SSMS does.

There might be ways to merge the rows but I'd not recommend doing that. You really need to identify what the actual issue is and resolve that in order to maintain data integrity.

Pete