Full outer join with three tables

[mr_oli]

Hello!

I need your help 🙂

I want to join three tables into one with full outer join but to be honest I do not know how.

First I decided that I will join table 1 and table 2 and after that - table 3.

But after joining first two I received duplicated columns:

What is the best way to do that?

Thanks!

[v-alq-msft · Accepted solution]

Hi, @mr_oli 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. 

You may try creating a calculated table as below.

Table = 
ADDCOLUMNS(
    DISTINCT(
        UNION(
            DISTINCT(Table1[Column1]),
            DISTINCT(Table2[Column1]),
            DISTINCT(Table3[Column1])
        )
    ),
    "Column2",
    MAXX(
        FILTER(
            Table1,
            Table1[Column1]=EARLIER(Table1[Column1])
        ),
        [Column2]
    ),
    "Column3",
    MAXX(
        FILTER(
            Table2,
            Table2[Column1]=EARLIER(Table1[Column1])
        ),
        [Column3]
    ),
    "Column4",
    MAXX(
        FILTER(
            Table3,
            Table3[Column1]=EARLIER(Table1[Column1])
        ),
        [Column4]
    )
)

Result:

Best Regards

Allan

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

[Jimmy801]

Hello @mr_oli 

I think you are not joining the tables, but simple combining them.  Use Table.Combine for this

Here a example code

let
    Table1 = 
    let
	Source = #table
	(
		{"Column 1","Column 2"},
		{
			{"1a","a"},	{"1b","a"},	{"1c","a"}
		}
	)
    in
        Source,
    Table2 = 
    let
	Source = #table
	(
		{"Column 1","Column 3"},
		{
			{"1d","a"},	{"1z","a"},	{"1d","a"}
		}
	)
    in
        Source,
    Table3 = 
    let
	Source = #table
	(
		{"Column 1","Column 4"},
		{
			{"1z","a"},	{"1xx","a"},	{"1c","a"}
		}
	)
    in
        Source,
    Combine = Table.Combine ( {Table1, Table2, Table3})
in
    Combine

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mr_oli]

Ok, that should be easy if I will have 3 rows in table

But what if I will have couple of thousand of rows? I will have to put every unique data in advanded editor?

[Jimmy801]

Hello @mr_oli 

no, for sure not. This is just an example that everyone understands easily how it works. In your realy live example you have to substitute the tables with your real tables from Excel or any other datasource

Hope this helps

BR

Jimmy

[mr_oli]

---

@Jimmy801 wrote:

In your realy live example you have to substitute the tables with your real tables from Excel or any other datasource

---

Data is coming from sql database

there is really no solution for that directly in Power BI?

[Jimmy801]

Hello @mr_oli 

for sure, just create 3 queries that is quering your data from SQL. Create a new blank query where you combine your 3 queries with your raw data using Table.Combine

BR

Jimmy