Forum Discussion

Copper Contributor

Jul 08, 2023

Solved

Fields from table not appearing in Pivot

I created two tables from a power query but added fields with formulas to one of the generated table because it would have been too difficult to do them in power query. When I created a pivot table f...

excel

SergeiBaklan
Jul 10, 2023
Moh_Hanno

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.

Moh_Hanno

Copper Contributor

Jul 09, 2023

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.

SergeiBaklan

Diamond Contributor

Jul 10, 2023

Moh_Hanno

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.