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
Then we have a sheet per month, which is 30 or 31 sheets/per month which is in the following format:
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
To:
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
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
Date : 01/05/2021
Car: CITROEN C5
Guys your help is really appreciated.
Thanks in advance,
Best Regards,
RR
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 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 3 | id | date | car | driver | SHIFT | client receive time | client delivery time | ||
| 4 | 1 | 01/05/2021 | CITROEN C5 | MICHAEL | 1 | 10:00 | 11:30 | ||
| 5 | 2 | 01/05/2021 | PEUGEOT 2008 | 2 | 14:00 | 14:30 | |||
| 6 | 3 | 01/05/2021 | CITROEN C5 | GEORGE | 3 | 14:00 | 17:45 | ||
| 7 | 4 | 02/05/2021 | CITROEN C5 | JOHN | 1 | 09:00 | 12:00 | ||
| 8 | 5 | 02/05/2021 | PEUGEOT 2008 | JOHN | 2 | 08:00 | |||
| 9 | 6 | 03/05/2021 | PEUGEOT 2008 | MICHAEL | 1 | ||||
| 10 | 7 | 03/05/2021 | PEUGEOT 2008 | 2 | |||||
| 11 | 8 | 03/05/2021 | CITROEN C5 | 3 | |||||
| 12 | 9 | 04/05/2021 | PEUGEOT 2008 | 1 | |||||
| 13 | 10 | 04/05/2021 | PEUGEOT 2008 | 2 | |||||
| 14 | 11 | 04/05/2021 | PEUGEOT 2008 | 3 | |||||
| 15 | 12 | 05/05/2021 | PEUGEOT 2008 | 1 | |||||
| 16 | 13 | 05/05/2021 | CITROEN C5 | 2 | |||||
| 17 | 14 | 06/05/2021 | CITROEN C5 | 1 | |||||
| 18 | 15 | 06/05/2021 | CITROEN C5 | 2 | |||||
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 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 2 | Date | 01/05/2021 | ||||||
| 3 | Car | PEUGEOT 2008 | ||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | id | transaction id | Shift | Driver | Receive time | Delivery time | ||
| 8 | 1 | 2 | ||||||
| 9 | 2 | 2 | ||||||
| 10 | 3 | 2 | ||||||
| 11 | ||||||||
01 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| 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)),"") |
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 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 2 | Date | 01/05/2021 | ||||||
| 3 | Car | PEUGEOT 2008 | ||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | id | transaction id | Shift | Driver | Receive time | Delivery time | ||
| 8 | 1 | 2 | ||||||
| 9 | 2 | 2 | ||||||
| 10 | 3 | 2 | ||||||
| 11 | ||||||||
01 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| 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)),"") |
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 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 2 | Date | 01/05/2021 | ||||||
| 3 | Car | PEUGEOT 2008 | ||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | id | transaction id | Shift | Driver | Receive time | Delivery time | ||
| 8 | 1 | 2 | 2 | Empty | 14:00 | 14:30 | ||
| 9 | 2 | |||||||
| 10 | 3 | |||||||
| 11 | ||||||||
01 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B8 | 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)),"") |
Date : 01/05/2021
Car: CITROEN C5
| Power Table test.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| I | J | K | L | M | N | O | |||
| 2 | Date | 01/05/2021 | |||||||
| 3 | Car | CITROEN C5 | |||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | id | transaction id | Shift | Driver | Receive time | Delivery time | |||
| 8 | 1 | 1 | 1 | MICHAEL | 10:00 | 11:30 | |||
| 9 | 2 | 3 | 3 | GEORGE | 14:00 | 17:45 | |||
| 10 | 3 | ||||||||
| 11 | |||||||||
01 | |||||||||
Guys your help is really appreciated.
Thanks in advance,
Best Regards,
RR
