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

Tonyd789

Board Regular
Joined
Feb 6, 2011
Messages
89
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Tony,
I don't think I completely understand how your data is organised and exactly how your dashboard is meant to look but I will make a couple of suggestions anyway. My first step would be to combine the data in the 3 queries you have created using append to a new query. You can then sort and filter this as required. Only load this table to your dashboard sheet.
Personally when I used this approach for a safety dashboard I went one step further and used Pivot Tables. After combining the three queries into one and cleaning up the data to only keep the columns that were required, I selectively unpivoted the data columns where numerical analysis was required (e.g. lost time, days since last break etc.). I then loaded the results to the Data Model. It's then really easy to create Pivot Tables based on the DM filtering, sorting and grouping data as needed.
P.S. I am fairly certain your current approach will not work as the formula references will become invalid as the Tables refresh from the queries; as you have already discovered.
Peter
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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
Back
Top