Creating a reference table from other tables using power quesry

[MaryamSe]

Hi, 

I have 4 tables that they include differet columns with different names. However, all include a date column, and an ID. I want to create a new table in power query that I can use a refernce table in my tables relation that inlcude all the date and IDs from all other tables. The new tables should get updated anytime other tables get updated.

The updated tabels may have more or less rows fro each year.

[Anonymous]

Hi, @MaryamSe 

Based on your description, I used Excel to create two sub-tables, which have id and date columns, and each has other columns with different names, as shown in the following figure:

I use Power BI to connect to this workbook, as shown in the following image:

In Power Qury, you only need to append two or more tables, generate new tables, and then delete other columns, as shown in the following figure:

When I update sheet1 in Excel, this appended table is updated as well:

The appended table is also updated:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MaryamSe]

Hi!

Thank you very much! Solved. Howvere, I needed to move some of the columns bceuase the tables do not have teh same order. e.g. date is the third column on one and the tenth  in the secind one. Is there any way that I can select columns before appending? 

[Anonymous · Accepted solution]

Hi, @MaryamSe 

Thank you very much for your reply. If you want to append the previously selected column, you can use M code similar to the following

let
    Source1 = Sheet6,
    Source2 = Sheet1,
    Source3 = Sheet7,
    FirstTable = Table.SelectColumns(Source1, {"id","date"}),
    SecondTable = Table.SelectColumns(Source2, {"id","date"}),
    ThirdTable = Table.SelectColumns(Source3,{"id","date"}),
    Combined = Table.Combine({FirstTable, SecondTable,ThirdTable})

in
    Combined

First of all, the source1, source2, and source3 equal signs are followed by the query names on the left side of PowerQuery, as shown in the following figure:

Then use the Table.SelectColumns function to select the id and date columns in these tables. The selected columns are appended at the end. 

Here are some steps:

First, right-click on the blank space on the left side of the mouse to create a blank query

Open the advanced editor and paste the M code above:

You need to replace the name after the source1-3 equals sign above with your actual query name. You can also add more sources, similar to the ones above.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MaryamSe]

Hi, @Anonymous Thank you for your great help!