Power Query and formulas

Gentas

New Member
Joined
Jun 2, 2021
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
3iddatecardriverSHIFTclient receive timeclient delivery time
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
7idtransaction idShiftDriverReceive timeDelivery time
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: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)),"")

To:

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)),"")

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
7idtransaction idShiftDriverReceive timeDelivery time
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
7idtransaction idShiftDriverReceive timeDelivery time
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
7idtransaction idShiftDriverReceive timeDelivery time
8111MICHAEL10:0011:30
9233GEORGE14:0017:45
103
11
01



Guys your help is really appreciated.

Thanks in advance,
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
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
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.

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

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
Joined
Jun 2, 2021
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
till now i only see recommendations about your forum usage.
If you had actually read the rules that you agreed to abide by and followed them, then there wouldn't have been a problem.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top