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

[Tonyd789]

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

 

[peter789]

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

 

[sandy666]

this OP has many duplicated posts (not only with this topic)