Consolidate multiple table in single sheet with similar headers using power query or python

Question

In the excel sheet, I have multiple tables with a similar header. I need help in consolidating all the tables in a single one. My table structure is looking like as below.

Table 1:

project1	2018	2019	2020
plan	100	200	300
forecast	20	40	30

Table 2:

project2	2018	2019	2020
plan	400	500	600
forecast	40	50	60

output table:

projects	Actions	2018	2019	2020
project1	plan	100	200	300
project1	forecast	10	20	30
project2	plan	400	500	600
project2	forecast	40	50	60

I need to automate this either using python or power query so that I can use it in PowerBI for visualization

Kindly assist me with the correct approach and necessary steps.

Table real structure: input - https://imgur.com/OuUpimS
output- https://imgur.com/esb30xv

Answer 1

You need to add projects table to each table in Power Query. The first table would contain project1, the second would contain project2 in the new column. Next create a union of 2 tables (it's called combine in Power Query).

This is for the online version of Power Query, but you'll find (almost) the same for desktop:

Then to combine the 2 tables:

You can then fiddle in the advanced editor and do this dynamically for any number of Excel files in a folder, but that's out of scope of a simple question.

Answer 2

Using powerquery

Highlight each table and load into powerquery using

Data .. From Table/Range ... [x] my table has headers

To get the column header added as a repeating row, Add column .. custom column .. with name projects and formula

= Table.DemoteHeaders(#"Changed Type"){0}[Column1]

save and close using file ... close and load to .. only create connection..

full code for each table would be similar to this

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"project1", type text}, {"2018", Int64.Type}, {"2019", Int64.Type}, {"2020", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.DemoteHeaders(#"Changed Type"){0}[Column1])
in #"Added Custom"

Then just append the data from all the tables.

Data ... Get Data .. Combine Queries ... Append ... and then add each table

which will produce code similar to

let Source = Table.Combine({Table1, Table2, Table3}) in Source