Two identical data table sources, yet the information doesn't match

[MDean]

I am experiencing an odd situation in Power BI. I have two queries that are the exact same source but the data sets don't match. 

I have tried removing the source and refreshing the data. 

I have tried googling for some information but haven't been able to find an answer with a solution. 

[v-echaithra]

Hi @MDean ,

We just wanted to follow up to ensure that your issue has been fully resolved. If there are any outstanding questions or if you need further assistance with anything, please don’t hesitate to reach out. We’re always here and happy to help in any way we can.

Your satisfaction is important to us, and we want to make sure you have everything you need.

Warm regards,
Chaithra E.

[v-echaithra]

Hi @MDean ,

Just checking in to confirm that your issue has been resolved. If you have any remaining questions or need additional assistance, feel free to reach out, we're always here to help.

Best Regards,
Chaithra E.

[v-echaithra]

Hi @MDean ,

Thank you @MasonMA  for your inputs.
We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

Thank you,
Chaithra E.

[MasonMA · Accepted solution]

Well It sounds like from cached data as Power Query aggressively caches data at multiple levels. I'm not sure if cached data are cleared when you mention 'removing the source and refreshing the data', did you do this under Globa Permission under Data source setting? This happened to me once. 

Also under Global in Setting, did you disable 'Allow data preview to download in the background' and clear the cache. This is what MS team advised us when we were trouble shooting with them. 

[MasonMA]

@MDean 

Hi there, your description sounds quite general. There might be many causes and you may need to narrow down with below questions. 

1.Are both queries pointing to the exact same source, such as connection string, file path, etc.?

Are both queries connecting to the same table or view, or just the same database? Could one query point to a different schema, different SharePoint folder, or different snapshot of a file?

You can go to Power Query, right-click the query in Advanced Editor and compare the source step.

2.Are there any transformation steps applied in either query?

Did someone apply filters, removed columns, or transformed data types differently in one of the queries? Are nulls, blanks, or case-sensitive fields handled differently? You may go to Power Query, under View, Step Dependencies to visualize if steps diverge.

3.Are refresh times different?

Did one query refresh more recently than the other? Are you comparing stale cached data vs newly refreshed data?You may check last refresh timestamp in Desktop or Service.

4.Are there any relationships, measures, or filters affecting the visual layer?

Are the visuals pulling from the raw queries or via calculated tables/measures? Are page-level or report-level filters applied?

5.Is one of the queries referencing the other (directly or indirectly)?

Could one query be using the other via Reference or Merge in Power Query? Is there a chance of circular logic or stale linkage?

Once you have that information, you may try these actions:

1. Compare the Advanced Editor code

Look at the Source step, and the transformations, even small differences (e.g., Table.SelectRows) can change row counts.

2: Output both tables as visuals with row count

In Power BI, create a card visual that shows COUNTROWS(QueryA) and COUNTROWS(QueryB) side by side. This gives a quick gut-check.

3. In Power Query.

You can create a third query like this:

Table.Difference = Table.NestedJoin(Query1, {"ID"}, Query2, {"ID"}, "Diff", JoinKind.LeftAnti)

This shows you rows in Query 1 but not in Query 2. 

These are just the tips my Copliot gave me. Hope it helps:)

[MDean]

I know specifically which items are not found in the second query, as I have already done the comparison steps. 

Query1 is the exact same source as Query2, path and everything. 

Query1, going back to the first step where the table is populated, the known missing information is there. 

Query2, going back to the first step where the table is populated, the known missing information is not there. 

I have even tried to duplicate Query1 and duplicating Query1, the known information is missing.