Power Query - Getting value from matrix in another table

[Alice2222]

I'm looking for some help please with this scenario which is too complex for me to resolve with my power query knowledge (in excel I would use Index/Match formula to achive it).

Here's what I have:

Table1 I have 2 columns:

• Date of which record was created
• Country 

In Table2 I have the below matrix:

• First Column -> Date of which record was created
• Column Header -> all the possible countries

is there a function that will allow me to get into Table1 a new column containing the value corrisponding to the Date/Country match?

Example:

if Date of which record was created = 24/07/2023 & Country = Lu - Luxemburg -> FW23 soft launch
if Date of which record was created = 24/07/2023 & Country = Be - Belgium-> SS23 W3

Thanks

[v-yanjiang-msft · Accepted solution]

Hi @Alice2222 ,

According to your description, here's my solution.

Table:

Table(2):

1.Add a custom column in Table:

let a=[Country Key] in
Table.SelectColumns(Table.SelectRows(#"Table (2)",(x)=>x[Date of Which Record Was Created]=[Date of Which Record Was Created]),{a})

2.Expand the new added column.

3.Add another custom column:

List.Max({[#"Custom.FR-France"],[#"Custom.DE-Germany"],[#"Custom.BE-Belglum"]})

4.Remove other unneeded columns, get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

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

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...