Query table does not match Query Preview

[orangehybrid]

I have four sets of data split into four different tabs, each tab with the same columns and column names. I'm trying to create a pivot table that is based on an amended query of the four tabs, created through Power Query. When i look at the Query Preview at the merged table, the numbers look right and match the values shown in the tabs. However, when loaded into a new tab, some of the values (e.g., dwelling units for apartments and other residential land uses in 2027) no longer match what's in the tabs. I'm very confused. I thought it might be some of the original tab values were showing up in a text format, but that wasn't true. Somehow, the query table and pivot table are accurate for the first of the four tabs, but not the rest.

See Sheet 1 for the loaded query table.

Please help!

 

[chirayu]

I don't see any mismatch for 2027 dwelling units against the merged table, so unsure what you're referring to

 

[GraH - Guido]

Seems all is okay after a refresh of the query and a refresh of the pivot.

[EDIT] if you would load the combined table as a connection only and make your pivot on this virtual table, you won't run into that problem.
See sheet 1... Initial set-up of the pivot made on the virtual table.

 

[orangehybrid]

I've tried to load the table as a connection only, but it gives me the same problem. I've attached screenshots where the discrepancies are shown.

 

[GraH - Guido]

Made this simple pivot and created the filters like you and got this (same result)



are you confused by the "number format"... <> "actual number in cell"?

 

[orangehybrid]

That is really strange. I did the same pivot as you and got a different result. See attached.

 

[GraH - Guido]

Can you click [refresh all] twice (as suggested in #3).

 

[orangehybrid]

I did (clicked refresh all and refresh multiple times), but I'm still missing the same values...

 

[GraH - Guido]

I just opened your workbook in #6 and pressed Refresh All. The pivot updated, but still gave a wrong result. I pressed Refresh All again, and only then the pivot shows the correct result.

I updated the power query so you'd only have a single query and a single table loaded as connection only. Made a pivot based on that one (Excel makes a second instance of the query, which is used in the pivot, so do not delete it).

So when you now press refresh, the pivot update triggers the query refresh first.

That should solve your issue.

 

[orangehybrid]

Thanks, I see that it works in the excel file you attached. I recreated the pivot table via a connection-only table in my live file, but I was still missing values. Then, I deleted all queries and recreated just one combined query, versus having a combined merged query based on the four other query tables. I used the advanced editor and copied the query code from your file. That seemed to work.

I'm so stumped by why having the merged queries would give me missing values. So weird.

Thanks for your help!

 

[GraH - Guido]

You're welcome.
What I believe happens in your file is:
- Pivot is based on the combined query, that is the result of a previous data set (source tables contain new records)
- Refresh 1 will update the pivot first and then update the queries. As the combined query is based on 4 connection only queries it will refresh with the new data.
- Refresh 2 will update pivot and queries again, but now the source data of the pivot already contains the new data and thus the pivot updates "correctly".

I don't think it's weird. Though Microsoft should solve the "order of refresh" logics that the Excel calculation engine follows. You might want to raise a user voice. I just upvoted the idea. As you will see, you're not alone facing the issue.

UserVoice Pages

excel.uservoice.com

 

[orangehybrid]

Great to know! I've upvoted the idea as well.