Forum Discussion
Fields from table not appearing in Pivot
Oops, sorry, I misunderstood, thought you added columns to the source table.
If columns with formulae are added to the table returned by Power Query, you may not load this query to data model. Instead query again resulting table with added columns, load this query to data model only and now build PivotTable from data model.
Please note, in general added columns are not in sync with returned by first query records. If you add formulae only the have references on the values in same row, thus it shall be no problems. If you add any texts (e.g. some comments) they are not in sync. If that is the case workaround exists, you may google for self-referencing queries, but it works only if you have / may create unique ID for your records.
May I clarify:
- you Power Query two tables from the grid loading them to data model
- you created PivotTable from data model
- you added more columns to one of the tables
- you did Refresh All
- you see no new Fields in PivotTable
Correct?
And does it help if to do Refresh All couple of times?
This is all true and yes, several refreshes do not solve the problem.
I would add:
When I create a pivot from one of the tables (the one with the extra fields) as opposed to the data model the fields appear. So I assume that the added fields are added to the sheet but not to the data model.
Strange. If you work with structured table added columns to it shall be added to data model as well.
Initially
and in data model
With added column to the source table, assigning proper data type in Power Query
and Refresh All after that it works
- I think I might not have explained properly how I added the two columns. I did not add them through the Query Editor. I found it difficult to write the formulas (running totals based on a criteria in another field) in the Query language so I added them to the resulting sheet that was generated after I save and loaded the query.
Oops, sorry, I misunderstood, thought you added columns to the source table.
If columns with formulae are added to the table returned by Power Query, you may not load this query to data model. Instead query again resulting table with added columns, load this query to data model only and now build PivotTable from data model.
Please note, in general added columns are not in sync with returned by first query records. If you add formulae only the have references on the values in same row, thus it shall be no problems. If you add any texts (e.g. some comments) they are not in sync. If that is the case workaround exists, you may google for self-referencing queries, but it works only if you have / may create unique ID for your records.