Outer join not showing expected results

[Anonymous]

Hi,

I would like to create a join that looks like this:

Table A		Table B				Expected Result
Period		Vessel	Period	Value		Vessel	Period	Value
01-Jan-19		Vessel A	01-Oct-19	1		Vessel A	01-Jan-19
01-Feb-19		Vessel A	01-Nov-19	2		Vessel A	01-Feb-19
01-Mar-19		Vessel A	01-Dec-19	3		Vessel A	01-Mar-19
01-Apr-19		Vessel A	01-Jan-20	4		Vessel A	01-Apr-19
01-May-19		Vessel A	01-Feb-20	5		Vessel A	01-May-19
01-Jun-19		Vessel A	01-Mar-20	6		Vessel A	01-Jun-19
01-Jul-19		Vessel A	01-Apr-20	7		Vessel A	01-Jul-19
01-Aug-19		Vessel A	01-May-20	8		Vessel A	01-Aug-19
01-Sep-19		Vessel A	01-Jun-20	9		Vessel A	01-Sep-19
01-Oct-19		Vessel A	01-Jul-20	0		Vessel A	01-Oct-19	1
01-Nov-19		Vessel A	01-Aug-20	1		Vessel A	01-Nov-19	2
01-Dec-19						Vessel A	01-Dec-19	3
01-Jan-20						Vessel A	01-Jan-20	4
01-Feb-20						Vessel A	01-Feb-20	5
01-Mar-20						Vessel A	01-Mar-20	6
01-Apr-20						Vessel A	01-Apr-20	7
01-May-20						Vessel A	01-May-20	8
01-Jun-20						Vessel A	01-Jun-20	9
01-Jul-20						Vessel A	01-Jul-20	0
01-Aug-20						Vessel A	01-Aug-20	1
01-Sep-20						Vessel A	01-Sep-20
01-Oct-20						Vessel A	01-Oct-20
01-Nov-20						Vessel A	01-Nov-20
01-Dec-20						Vessel A	01-Dec-20

I did a full outer join on Period (Table A) = Period (Table B). However, my result is always showing the same as Table B, and not what I expected on the last table. Anyone know if I am doing this the right way? Please advise. Thanks!

[Mariusz]

Hi @Anonymous 

You probably need to have an extra table with Vessels and add it to your period table as an extra column, later you will need to merge on Date and Vessel to get your result.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[HotChilli]

Left outer join won't bring the "Vessel A" text into every row.

If you add a column to Table A before the join:

="Vessel A"

That should do it.

I suspect the real data is a bit more complex

[Anonymous]

Hi,

Thanks for your reply. I am trying to do a full outer join here in fact.

And you are right, the real data is much more complex, with several vessels and each have different period. I need all of the vessels to conform to the same period as Table A. I expect to see blank values for rows that do not yet exist in Table B

[v-diye-msft]

Hi @Anonymous ,

Please try below the M code:

let
    Source = Table.NestedJoin(TableA, {"Period"}, TableB, {"Period"}, "TableB", JoinKind.LeftOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Vessel", "Period", "Value"}, {"TableB.Vessel", "TableB.Period", "TableB.Value"}),
    #"Sorted Rows" = Table.Sort(#"Expanded TableB",{{"Period", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"TableB.Vessel", "TableB.Period"})
in
    #"Removed Columns"

[Mariusz · Accepted solution]

Hi @Anonymous 

You can take your Vessel table like below.

Add to your period table like below.

then just expand vessels in into rows and merge wit table b

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Thanks Mariusz! This solution works for me. Adding column from a table instead of merging is something new to me, and that works well. Thanks for sharing and I've learnt something new and valuable!

[Mariusz]

Hi @Anonymous 

Happy I could help.

Best Regards,
Mariusz

Please feel free to connect with me.