Pulling data from the power query into a spreadsheet not working correctly??!!

Tonyd789

Board Regular
Joined
Feb 6, 2011
Messages
87
Hi all

Although i'm not a novice when it comes to the excel side of things, and i can navigate myself round some pretty complex formulas, i have never dabbled in excels power query tool and have little to no knowledge of SQL queries either.

Despite that I've had a go and need some help please, so here we go with the attempt on explaining whats going on.....

I have 3 spreadsheet where i record all the sites accidents, incidents and near misses on, within each workbook there is a tab called Log where each row represents a new event. Each row has many columns with lots of information regarding the event.

I have created a separate spreadsheet called the dashboard to gather all this data/statistics into one place, this is where i have also built 3 separate data query tabs for the 3 logs pulling in the data.
I have edited what i'm pulling in, by deleting columns not wanted and also applying filters within the power query editor.

The filters are applied on site and open cases only, meaning they only show a few lines at most in the queries.

I have created a report tab where rows 1 to 15 reference certain cells from rows 1 to 15 on the accident query then rows 15 to 30 for incidents and rows 30 to 45 for near misses. I then filter out blank lines showing only the open cases for the site i work on. Its not quite exactly this layout but this is the easiest way to explain.

So on the report tab A1 will reference cell A1 on the accident query tab, using a if(A1="","",a1 kind of formula. and so on and so forth for each row up to a max of 15 rows for each query.

It all works great up until the query lines change info, such as one has closed and / or a new one opened. My report tab row will REF out, or change rows all together. meaning i'm missing data within the report instead of just pulling in the data within the cell.

I hope that makes sense to someone that can help me out with this very annoying problem.

Regards Tony
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,814
It's hard to say something more without seeing a representative example, but forget about everything you learned about formulas and try to think in PowerQuery language. Try to do it in PQ as much as you can.
Mixing formulas and PQ is not a good idea. Even if you meet the wall.
Unless your query tables are static and do not change after refreshing.

this is my humble point of view :)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top