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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

peter789

Board Regular
Joined
Nov 20, 2016
Messages
114
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,742
this OP has many duplicated posts (not only with this topic)
 

Watch MrExcel Video

Forum statistics

Threads
1,101,935
Messages
5,483,779
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top