topic Re: Power Query - Getting value from matrix in another table in Power Query

Power Query - Getting value from matrix in another table

Alice2222 — Sat, 22 Jul 2023 14:46:40 GMT

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

Re: Power Query - Getting value from matrix in another table

lbendlin — Sat, 22 Jul 2023 22:13:24 GMT

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-Forum/ba-p/963216
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/1447523

Re: Power Query - Getting value from matrix in another table

v-yanjiang-msft — Tue, 25 Jul 2023 07:29:13 GMT

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.