# Power Query and formulas

#### Gentas

##### New Member
Dear All,

We are using Office 365 and have created an excel file where using GET DATA FROM SQL SERVER Database we are importing a view from a database:

The table has the following headers and data according the data of the specific Car and the month

Power Table test.xlsx
ABCDEFG
4101/05/2021CITROEN C5MICHAEL110:0011:30
5201/05/2021PEUGEOT 2008214:0014:30
6301/05/2021CITROEN C5GEORGE314:0017:45
7402/05/2021CITROEN C5JOHN109:0012:00
8502/05/2021PEUGEOT 2008JOHN208:00
9603/05/2021PEUGEOT 2008MICHAEL1
10703/05/2021PEUGEOT 20082
11803/05/2021CITROEN C53
12904/05/2021PEUGEOT 20081
131004/05/2021PEUGEOT 20082
141104/05/2021PEUGEOT 20083
151205/05/2021PEUGEOT 20081
161305/05/2021CITROEN C52
171406/05/2021CITROEN C51
181506/05/2021CITROEN C52
PowerQuery

Then we have a sheet per month, which is 30 or 31 sheets/per month which is in the following format:

Power Table test.xlsx
ABCDEF
2Date01/05/2021
3CarPEUGEOT 2008
4
5
6
812
922
1032
11
01
Cell Formulas
RangeFormula
B8:B10B8=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)),"")

We have two major problems that we will really appreciate your help:

1. As you will see on the sheet '1' we have a formula/per cell in order to get the right value for the specific cell from the power table. The problem that we are facing is the following, when the view that we are importing has 0 rows, the formulas on Sheet "1" remains OK, as soon as we connect with a database which has data (for example 20 rows) then after datasource change and the refresh all data the formulas in the sheet "1" auto-change.

For example the Power-Query sheet after the refresh gets 20 rows of data then the formula on Sheet '1' changes from
 B8:B10 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:B10 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 is does is that start the count of rows from the end of the imported rows on PowerQuery, which in our case is 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 uploaded everyday we are quite unsure if the excel will change the formulas by itself.

2. On sheet "1" we have a default template as below

Power Table test.xlsx
ABCDEF
2Date01/05/2021
3CarPEUGEOT 2008
4
5
6
812
922
1032
11
01
Cell Formulas
RangeFormula
B8:B10B8=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)),"")

On Sheet "1" we have standard 3 rows per day (which are the maximum shifts (3) of the car per day)
On each row the formula must show the information matching the Car and Date with the columns on Power-Query Sheet and show the appropriate values

For example for cell B8:B10
We have tries both the formula
B8:B10B8=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)),"")
which returns on every row the same value
and the formula
=IFERROR(INDEX(PowerQuery[id]; SMALL(IF((\$D\$2 & \$D\$3 = PowerQuery[date] & PowerQuery[car] - ROW(PowerQuery[id] - ROW(PowerQuery\$A\$4)+1); ROW(1:1))); "")

which doesnt work.

The desired result on Sheet "1" according the PowerQuery Sheet data should be :

Date : 01/05/2021
Car: PEUGEOT 2008

Power Table test.xlsx
ABCDEF
2Date01/05/2021
3CarPEUGEOT 2008
4
5
6
8122Empty14:0014:30
92
103
11
01
Cell Formulas
RangeFormula
B8B8=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)),"")

Date : 01/05/2021
Car: CITROEN C5

Power Table test.xlsx
IJKLMNO
2Date01/05/2021
3CarCITROEN C5
4
5
6
8111MICHAEL10:0011:30
9233GEORGE14:0017:45
103
11
01

Guys your help is really appreciated.

Best Regards,
RR

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### Fluff

##### MrExcel MVP, Moderator
Welcome to the MrExcel Message Board!

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.

Cross posted at: Power Query Data Source change, reacts on cell formulas
If you have posted the question at more places, please provide links to those as well.

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

#### Gentas

##### New Member
Thanks for your remarks, but i need some help on my post, and till now i only see recommendations about your forum usage.

#### Fluff

##### MrExcel MVP, Moderator
If you had actually read the rules that you agreed to abide by and followed them, then there wouldn't have been a problem.

Replies
2
Views
215
Replies
8
Views
182
Replies
2
Views
76
Replies
1
Views
36
Replies
8
Views
103

1,148,156
Messages
5,745,101
Members
423,923
Latest member
yuvaraj859

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back