Excel Table Not Updating

[AngieNaude]

I'm sure there's another thread but I really need a quick answer on this one. I'm building a excel table through power query that holds the full data from 4 tables but when I update one of the smaller tables, it doesn't update the big table with all data in it. Please help?

 

[Alex Blakenburg]

To use XL2BB it should show Xl2bb as a tab towards the far left of the menu.
If if does, highlight the area to be copied.
Click on Xl2bb then click on Mini Sheet
Then copy it into your reply box.
It will look undecipherable at first until you actually post it or click on the preview button.

More Instructions on using this tool can be found here: XL2BB Add-in

Can you also please post a picture of what you see in Power Query when you click on View > Query Dependencies ?

Are your "4 smaller" tables being created by Queries ? or are they stand alone tables in the same workbook ?

 

[AngieNaude]

To use XL2BB it should show Xl2bb as a tab towards the far left of the menu.
If if does, highlight the area to be copied.
Click on Xl2bb then click on Mini Sheet
Then copy it into your reply box.
It will look undecipherable at first until you actually post it or click on the preview button.

More Instructions on using this tool can be found here: XL2BB Add-in

Can you also please post a picture of what you see in Power Query when you click on View > Query Dependencies ?

Are your "4 smaller" tables being created by Queries ? or are they stand alone tables in the same workbook ?

@Alex Blakenburg and @alansidman. Here is the sample that I made up and it seems to be working.
@Alex Blakenburg I am using sheets from 4 other workbooks for each individual customer that I am querying into another workbook called full data. I have found that making a full data table there creates new sheets of the tables in the 4 other workbooks. I appended them all into a full data tab.

I have found that if I make tables in one of those other customer workbooks and go to queries and connections, even though I don't see any connections, after I have added a line, I click refresh. Then I close the customer workbook.

Then I go into my full data workbook, I have to refresh each of those individual sheets that were created and only THEN can I go into my full data sheet and refresh that.

Look it works but there is a lot of refreshing going on and a lot of sheets being created. If I lose a connection, I find I have to add a new source and then it creates ANOTHER sheet! I get so confused

Seafood table in customer workbook

Test table.xlsx
	C
6	soles
Seafood

Clothes table in customer sheet

Test table.xlsx
	C
6	jackets
Clothes

Full data table in full data workbook

Full data.xlsx
	E
23
Seafoodtable

But look at the number of tabs it has created!

The appended full data table has somehow gone under the seafoodtable tab.

As I said, it sort of works but I just want to know the best possible way to create these and use them. This is small data but the ones I am trying to use are sales for a year from 4 customers!

 

[Alex Blakenburg]

I don't if @alansidman, is able assist based on the above but I am unable to follow the process from that.
Can you answer the question in the picture below:-

 

[AngieNaude]

I don't if @alansidman, is able assist based on the above but I am unable to follow the process from that.
Can you answer the question in the picture below:-

View attachment 44570

4 different workbooks for 4 different customers
I add the last month's sale at the beginning of each month
Each customer has a different format of how they send the sales so there are a lot of formulas to standardise them
I paste the standardised new data at the bottom of a table in a sheet called ongoing data in their individual workbooks
The Full Data workbook pulls data from each of these for ongoing sheets in the four customers' workbooks
When I create a table for full data in a full data workbook, and query the separate workbooks, I get those same ongoing sheets duplicated in the new full data workbook
I then append the sheets and get another set of duplicated sheets
And I have to refresh the sheets in the different customer workbooks, then refresh the duplicated sheets in the full data workbook, then refresh the appended full data sheet.
In the end, how much less work is this than just pasting it a the bottom of a table where I can run a pivot off?

 

[Alex Blakenburg]

• Customer sends in the data in different formats
You might be able to use Power Query (PQ) to convert it to a standard layout.
It would still probably be easier to manually append it to the OnGoing Data sheet though.
And any queries in these workbooks would require the workbook to be opened and the Refresh All run.

• Full Data workbook
a) You only need to pull these 4 Ongoing Data sheets into the Full Data workbook separately if you want them replicated in Full Data workbook.
If you have pulled them in as separate sheets using separate queries, you can them use the queries and append them as "New".
This will avoid you issue of replicating them again.
This will create a new query that appends the other 4 and outputs a single table to a sheet of your choosing.

b) If the 4 workbooks are all in the one folder then you should be able to pull all 4 Ongoing Data Sheets straight into a consolidated sheet, without replicating them in the Full Data workbook.

In the Full Data workbook a single "Refresh All" should update all the queries if you are using a) and in b) there should only be one query

We can help with the queries but we would need to know which data you want shown in which workbook and sample data of the data that needs to be imported.
You will have to do less rework if you give real workbook names and sheet names. It would also be easier if you are using Excel Tables to store the data with meaningful table names and tell us what the names are.

 

[AngieNaude]

• Customer sends in the data in different formats
You might be able to use Power Query (PQ) to convert it to a standard layout.
It would still probably be easier to manually append it to the OnGoing Data sheet though.
And any queries in these workbooks would require the workbook to be opened and the Refresh All run.

• Full Data workbook
a) You only need to pull these 4 Ongoing Data sheets into the Full Data workbook separately if you want them replicated in Full Data workbook.
If you have pulled them in as separate sheets using separate queries, you can them use the queries and append them as "New".
This will avoid you issue of replicating them again.
This will create a new query that appends the other 4 and outputs a single table to a sheet of your choosing.

b) If the 4 workbooks are all in the one folder then you should be able to pull all 4 Ongoing Data Sheets straight into a consolidated sheet, without replicating them in the Full Data workbook.

In the Full Data workbook a single "Refresh All" should update all the queries if you are using a) and in b) there should only be one query

We can help with the queries but we would need to know which data you want shown in which workbook and sample data of the data that needs to be imported.
You will have to do less rework if you give real workbook names and sheet names. It would also be easier if you are using Excel Tables to store the data with meaningful table names and tell us what the names are.

Thanks so much Alex. I shall try what you say and let you know if I run into more trouble. The customer different formats thing is just fine as I do have my formulas in place. To be honest I could just add the next sales under the big table but I really don't like doing things manually without using all that Excel has to offer. I don't like doing things manually. Feels so stone agey?

 

[Alex Blakenburg]

Definitely let us know, if you need any help progressing this.

The link below is to page has a 9 min video on consolidating data from separate workbooks into a consolidation workbook (also has written instructions)
Combine Data from Multiple Workbooks in Excel (using Power Query)

This version relies on the data (probably your Ongoing Data sheets) using Excel Tables.

He has links at the bottom of the page that also cover the other common scenarios
• data being in the separate workbooks but not being in Excel Tables, so getting the data from an Excel sheet
• data being in separate sheets in the same workbook used for the consolidation.
(Tables are always preferred though)