Power Query Data Source change, reacts on cell formulas

[Gentas]

Dear All,

We are using Excel 365, and we created an excel which imports data from SQL Server using Power Query, and on each sheet the cells are updated with formulas.

What we have observed and we dont know how to fix is that when we change datasource to a Database which has no data inside the formulas on sheets remains OK, but when we change the datasource and there are data inside the power query table then the formulas on other sheets cells change:

Example

For example the Power-Query sheet after the refresh gets 20 rows of data then the formula on Sheet '1' changes from
B8:=IFERROR(INDEX(PowerQuery!$A$4:PowerQuery!$A$100, XMATCH($D$2 & $D$3,PowerQuery!$B$4 : PowerQuery!$B$100 & PowerQuery!$C$4 : PowerQuery!$C$100,1)),"")

To:

B8=IFERROR(INDEX(PowerQuery!$A$24:PowerQuery!$A$100, XMATCH($D$2 & $D$3,PowerQuery!$B$24 : PowerQuery!$B$100 & PowerQuery!$C$24 : PowerQuery!$C$100,1)),"")

What it does is that changes the formula of the cell from $B$4 to $B$24 and as a result we dont get the accurate data on cells.

Is there a way to correct this issue, because the database will start from 0 and as rows are added everyday we are quite unsure if the excel will change the formulas by itself.


Thanks in advance.

Best Regards,

RR

 

[Fluff]

Duplicate to: Power Query and formulas

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.

 

[Joe4]

Also Cross posted at: Org Structure in Pivot

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.