The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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!
Solved! Go to Solution.
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.
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
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
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"
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!
User | Count |
---|---|
68 | |
63 | |
59 | |
54 | |
28 |
User | Count |
---|---|
182 | |
81 | |
64 | |
46 | |
41 |