Expand two tables with transition table

[Markus_P]

Hello

I have a problem, and I don’t know how to solve it (automated)

To explain the problem, I have created a simplified case:

I get regular a table with some information’s. But recently they have changed the Table structure. They have renamed, added, and removed some columns. But for my task I need to “combine / append” these tables (Timeseries). I have the Information that connect the new with the old format. The goal is to show all the data in Power Bi. I had the Idea to load the old, new and transition table in Power Bi and then connect them. But I was not able to pull this off. Then I thought I could do this automated in excel and combine these into one table and then load it in Power BI. The problem that I am facing is that I get regular new content, so the table get some new rows, so the connection had to be dynamic (otherwise I could do the task one time manual).

Here a picture of the simplified Situation. The blue Table is the old structure. The green the new one. The green will get longer over time. I need to append the green table automatically to the blue / or otherwise. To know with Colum from the green table is connected to the blue table I have the table to the right. Note, that some columns have not changed name, some have, some have been added and some have been removed.

Has someone an idea?

Thanks

[Markus_P]

Thanks for the good replys. Sorry for the waiting time, I was on vacation.

This sounds if it could work. I will try it and give an update.

Thanks

[BA_Pete]

Hi @Markus_P ,

Bring your old table into Power Query.

Change any column names in this table to match the renamed columns in you new table.

Remove columns no longer required from both queries.

Append old and new tables.

All the append function needs to work properly is two tables with the same column names (exact, including capitalisation) and the same data types. So, as long as you rename the columns in your old table to their new names, they'll match fine with the new table.

Any columns that are for data no longer collected in the new table will just show as nulls against the new table rows. Conversely, any columns that are for data not collected previously in the old table will just show as nulls against the old table rows.

Pete

[Mahesh0016 · Accepted solution]

@Markus_P 
as per my understanding, you will try the folder as a source in power bi and you pull all Excel in a folder for combination / Append in power bi. Thank You!