Forum Discussion
Data Model in Service duplicating and replacing rows
Hi Ed. As I said over email, we've identified the root cause of this issue. We're currently targeting the fix for our July release.
In the meantime, you can work around the issue by ensuring that your folded queries are always sorted. This can be done by always including (and preserving in downstream queries) all primary keys for a given table. Or, if this isn't possible (as in your case when doing a Group By operation), you can explicitly sort by a set of unique keys before combining. (Also note that it may be necessary to buffer after sorting and before combining to ensure the sort is applied by the folding layer. You'll have to experiment to see whether or not this is necessary.)
Ehren
I ran a test and this is what I am seeing:
- The table that shows the error is a result of a Table.Combine in Power Query.
- I took the two source queries to that and loaded them into the data model.
- I then used a DAX UNION() statement to generate a new table in Power BI.
- I build a new measure in the new DAX table to replicate a mesaure in the table loaded from the Table.Combine query and stuck it on a card.
- Refreshed everything in Power BI Desktop. Both the measure from the orginal table and the measure from the DAX UNION function show the same result.
- Published to my workspace, connected to the Gateway and refreshed.
- The measure in the DAX UNION table works fine.
- The measure in the original table is off by these random rows.
Something isn't right with how the Table.Combine statement is being loaded either by the Gateway or being handled by the service. The desktop app is fine.
Hi Ed. I've tried reproducing this locally (using SQL data refreshed via a Gateway) and am unfortunately not seeing any duplication. Would it be possible for you to try some other things for me?
- Do your SQL queries involve passing native SQL statements (e.g. "select * from myTable"), or do they simply connect to the tables? What transforms (if any) are you doing on top of the SQL data?
- Try duplicating the query that does the Table.Combine operation, and then buffering the result (i.e. add a step at the end that does = Table.Buffer(previousStep)). Does this version of the query exhibit the same behavior?
- Try duplicating the query that does the Table.Combine operation, and buffering each individual table being combined (e.g. = Table.Combine({Table.Buffer(Query1), Table.Buffer(Query2)})). Does this version of the query exhibit the same behavior?
- Try duplicating the query that does the Table.Combine operation and doing a group by and count of the unique field you're seeing duplicated, then filter for Count > 0 (which should return an empty table when run locally in PBIDesktop). When refreshed via the gateway, does this query produce any results?
I'm trying to pinpoint if the issue is happening on the M query side of things or somewhere in the AS pipeline.
Thanks for your help.
Ehren