How to combine multiple tables in power BI

Question

I have three tables whose structure is as follows-

Table 1-

Table 2-

Table 3-

I wanted to have a table like this -

I tried this particular formula for creating a calculated table-

Table = UNION(SELECTCOLUMNS(Table1,"Table1", Tables[Table1]), SELECTCOLUMNS(Table2, "Table2 Totals", Table2[Totals]), SELECTCOLUMNS(Table3, "Table3 Totals", Table3[Totals]))

The result I am getting is like this:-

I have no idea why this is happening. What to do in this scenario? Do calculated columns not come in merge query?

Answer 1

Hi Sindhu,

You can merge the 3 tables into a new table to get the required output that you want.

1.  Merge TABLE1, TABLE2.  ---------->     NEW TABLE    [ columns - Table2, Name ]

2.  Select all column from TABLE1.  Select 1st and last column of TABLE2 IN THE NEW TABLE.

3.  Merge TABLE3, NEW TABLE  ------------->   NEW TABLE.     [ columns - Table3, Name ]

4.  Select only 1st column from TABLE 1 And last columns of TABLE  2, TABLE 3.

5.  You will get the required result.

Hope this helps you.

If you are interested in learning more about Power BI then check out Power BI Training now!

Answer 2

n Power BI Desktop you can join two tables with Merge menu item in the Query Editor, in Home tab, Under Combine, Merge Queries. The Merge Window will appear with ability to select first table (Left part of the join), and the second table (Right part of the join).

Elevate your data analysis skills to expert levels with our in-depth Mastering Power BI Program. Gain the advanced expertise to analyze, visualize, and report data like a pro.

Answer 3

Here are the steps to merge these tables:

1. Click on the Data tab.
2. In the Get & Transform Data group, click on 'Get Data'.
3. In the drop-down, click on 'Combine Queries.
4. Click on 'Merge'. ...
5. In the Merge dialog box, Select 'Merge1' from the first drop down.
6. Select 'Region' from the second drop down.

Unlock the full potential of your data visualization with custom tables in Power BI! Learn how to create tailored tables that suit your unique analytical needs and enhance your business insights. Start building your custom tables now!

Answer 4

Click Advanced Editor and check if step #"Appended Query" is like pattern below:

#"Appended Query"Table.Combine({#"Changed Type", Table2, Table3, ...}),