Forum Discussion

mr_oli's avatar
mr_oli
Helper I
6 years ago
Solved

Full outer join with three tables

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's avatar
    v-alq-msft
    6 years ago

    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.

6 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
    Jimmy801
    Community Champion
    6 years ago

    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's avatar
      mr_oli
      Helper I
      6 years ago

      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's avatar
        Jimmy801
        Community Champion
        6 years ago

        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