Create a query appending three queries

[TopLearner]

Hello all,


I want to create a table on a spreadsheet where I can combine multiple tables from the source below:

Almeria statistics, latest results and form 2022/2023


I go to data then from web, then I click on the folder

Then, transform data and my goal is that the table below, shows the same data together as the three other queries (please see the right-hand side table at the bottom of the post)

Then, each week when I refresh the data after the football games, Id like my appended query above to get the right data.


Can you please help?

Thanks
Sanchez

 

[KRice]

I thought my previous post already answered this? See...

Power Query append tables and sort columns

Good evening, I hope you are well. I have a file where I have a tab called Append1 which is the result of appending the three tables for Almeria on another tab Can you please help me so the cells in column Scoring and Column1 move under Current sequences and the numbers in column all move to...

www.mrexcel.com

It appears that you want the final table to have a 1st column heading of "Options" rather than "CURRENT SEQUENCES"...that is the only difference between my earlier suggestion and what is posted above. After you have loaded the three tables into Excel, note what the names of the tables are, and then use Power Query to create "connection only" queries for each of the three. Then in Power Query change the column heading names where necessary so that each table has headings of Options, Home, Away, Total. Then append all three tables. Adjust the append order in the Table.Combine command so that the results appear in the order desired.

 

[TopLearner]

I thought my previous post already answered this? See...

Power Query append tables and sort columns

Good evening, I hope you are well. I have a file where I have a tab called Append1 which is the result of appending the three tables for Almeria on another tab Can you please help me so the cells in column Scoring and Column1 move under Current sequences and the numbers in column all move to...

www.mrexcel.com

It appears that you want the final table to have a 1st column heading of "Options" rather than "CURRENT SEQUENCES"...that is the only difference between my earlier suggestion and what is posted above. After you have loaded the three tables into Excel, note what the names of the tables are, and then use Power Query to create "connection only" queries for each of the three. Then in Power Query change the column heading names where necessary so that each table has headings of Options, Home, Away, Total. Then append all three tables. Adjust the append order in the Table.Combine command so that the results appear in the order desired.

Hi KRice,

Thanks for your help.

Will I have to change the queries headings each time the data on the source changes when refreshing the queries? For instance, for the first table will I have to replace "Options" with "CURRENT SEQUENCES" so it gets the most updated data?

My query

Table on the website

Thanks
Sanchez

 

[KRice]

If I understand your question correctly, the answer is "no". If your source information on the web is consistent, you will create a query to pull those tables into Excel (you're already doing this). But within those queries, do one more thing: as one of the steps, change the column heading names so that each table has headings of Options, Home, Away, Total. That step will become part of the web scraping query so you should need to do this only once for setting up the query. Also, rather than loading each of the three tables to your worksheet, you may want to load the queries as "Connection only" so that you don't see them. Once you have the three source tables loaded into PQ as Connection only and you have a step in each of those queries to revise the column headings (so that they are all consistent), you will want to execute Home > Append Queries > Append Queries As New and add the three connection only queries to it in the order desired, and then load that appended query back into your worksheet as a table. After this initial setup, you shouldn't have to manually change the headings for subsequent updates.

 

[TopLearner]

If I understand your question correctly, the answer is "no". If your source information on the web is consistent, you will create a query to pull those tables into Excel (you're already doing this). But within those queries, do one more thing: as one of the steps, change the column heading names so that each table has headings of Options, Home, Away, Total. That step will become part of the web scraping query so you should need to do this only once for setting up the query. Also, rather than loading each of the three tables to your worksheet, you may want to load the queries as "Connection only" so that you don't see them. Once you have the three source tables loaded into PQ as Connection only and you have a step in each of those queries to revise the column headings (so that they are all consistent), you will want to execute Home > Append Queries > Append Queries As New and add the three connection only queries to it in the order desired, and then load that appended query back into your worksheet as a table. After this initial setup, you shouldn't have to manually change the headings for subsequent updates.

Thanks KRice,

This is what I have done so I will ensure it works when the figures change on the data source.

Kind regards
Sanchez

 

[KRice]

Are these the three tables you are interested in?