• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Query table does not match Query Preview

orangehybrid

New Member
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!
 

Attachments

GraH - Guido

Well-Known Member
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.
 

Attachments

Last edited:

GraH - Guido

Well-Known Member
Made this simple pivot and created the filters like you and got this (same result)

61342

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

GraH - Guido

Well-Known Member
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.
 

Attachments

orangehybrid

New Member
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

Well-Known Member
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.
 
Top