Fields from table not appearing in Pivot

[Moh_Hanno]

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 from the data model these fields don't appear as a choice in the pivot field selector even though I added them to the table range. If I create a pivot from the sheet (not from the model) they appear but I loose the ability to select fields from the rest of the data model. 

I also tried to add the other sheet to this last pivot but I get an formula references error (which I guess is related to the added fields in the other sheet)  and if I try to create a relation it tells me that the relation already exists (I created it as part of the query).

[#2]

@Moh_Hanno 

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?

[#3]

@Sergei Baklan 

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.

[#4]

@Moh_Hanno 

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

[#5]

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.

[#6 · Accepted solution]

@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.