Add data from column (of other table) depending on content of both tables

[YouCanCallMeAl]

I want to add the content of another table depending on unique reference.

I have 2 Tables (Table1 and Table2).

I want to add a new custom column [newColumn] to Table1 with the [Date] from Table2, depending on [Quarter] and [Version] of both tables.

When [Quarter] and [Version] are identical in both tables, then the value from [Date] has to be copied into [newColumn].

Can you help?

Table1

Quarter	Version
Q1-Year01	unique-reference-101
Q2-Year01	unique-reference-101
Q3-Year01	unique-reference-101
Q4-Year01	unique-reference-101
Q1-Year02	unique-reference-101
Q2-Year02	unique-reference-101
Q3-Year02	unique-reference-101
Q4-Year02	unique-reference-101
Q1-Year01	unique-reference-102
Q2-Year01	unique-reference-102
Q3-Year01	unique-reference-102
Q4-Year01	unique-reference-102
Q1-Year02	unique-reference-102
Q2-Year02	unique-reference-102
Q3-Year02	unique-reference-102
Q4-Year02	unique-reference-102
…	…

and Table2

Quarter	Version	Date
Q1-Year01	unique-reference-101	01.01.2024
Q2-Year01	unique-reference-101	02.04.2024
Q3-Year01	unique-reference-101	03.07.2024
Q4-Year01	unique-reference-101	03.10.2024
Q1-Year02	unique-reference-101	03.01.2025
Q2-Year02	unique-reference-101	05.04.2025
Q3-Year02	unique-reference-101	06.07.2025
Q4-Year02	unique-reference-101	06.10.2025
Q1-Year01	unique-reference-102	01.01.2028
Q2-Year01	unique-reference-102	02.04.2028
Q3-Year01	unique-reference-102	03.07.2028
Q4-Year01	unique-reference-102	03.10.2028
Q1-Year02	unique-reference-102	03.01.2029
Q2-Year02	unique-reference-102	05.04.2029
Q3-Year02	unique-reference-102	06.07.2029
Q4-Year02	unique-reference-102	06.10.2029
…	…	…

[v-kpoloju-msft · Accepted solution]

Hi @YouCanCallMeAl,

Thank you for reaching out to the Microsoft fabric community forum. Thank you @SundarRaj, and @mussaenda, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @YouCanCallMeAl,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[SundarRaj]

Hi @YouCanCallMeAl 
Here's a solution you could look at. I'll leave the image of the output and M code used below. Thanks!

Here's the code used:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Table = Table.TransformColumnTypes(Source,{{"Quarter", type text}, {"Version", type text}}),
RefTable = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
RefCol = Table.AddColumn(RefTable, "RefCol", each [Quarter] & "-" & [Version]),
AddRefCol = Table.AddColumn(Table, "RefCol", each [Quarter] & "-" & [Version]),
AddDates = Table.TransformColumns(AddRefCol,{"RefCol", each Record.ToList(Record.SelectFields(RefCol{List.PositionOf(RefCol[RefCol],_)},"Date")){0}})
in
AddDates

[mussaenda]

Hi @YouCanCallMeAl ,

you can merge the table 2 to table 1 using two columns. Just need to do ctrl then select your second column upon merging. 

Hope this helps