Merge if meet condition or M code

[pedroccamara]

Hey all
I need to pick a column from another table (not related) if date > other date.

Fact table
Client ID, User ID, Time, Date

Lookup Table
Other Date, Value

The idea is to pick the value from lookup table if Date is => Other Date

I hope this wasn’t to confuse.
Thanks all of you
Best regards
Pedro

[BrianJ]

@pedroccamara,

If they’re not related, you should be able to use LOOKUPVALUE if date >= other date to pull the proper value from your lookup table.

(You can use LOOKUPVALUE even if the are related, but in that case it’s easier and more efficient to use RELATED)

• Brian

[Gifted]

Hello @pedroccamara.

To have the resolution you want using the M code it is necessary to try to join the two tables to bring the column that participates in the if condition.

Below is a topic that deals with the same subject. @Melissa and I solved the topic in question using different approaches.

Merge 2 tables contais

HI DNA Team, merge contains.xlsx (171.9 KB) I will appreciate your help if this is possible, I would like to merge both tables together in other to achieve the result below [image] The description column contains invoice number in the text string there that can be found at the beginning of the text, at the end, or in the middle. [image] The second table contains invoice numbers and the codes that I would like to add to my data. Is it possible to add the column “Code” to the first table i…

Best regards
Gifted

[BrianJ]

@pedroccamara,

Sorry - should have read the title more carefully and seen you were (appropriately) looking for a PQ solution, not DAX. Thus, @Gifted‘s response is obviously more relevant.

• Brian

[Melissa]

Hi @pedroccamara,

The filter logic in the topic referred to by @Gifted is different, however the same principle applies.
For example.

Here’s my sample file. eDNA - Merge if meet condition.pbix (20.8 KB)
I hope this is helpful

[pedroccamara]

Guys, you’re all the best!!!
Thank you so much for your help.
After your help, here’s my code:

BufferTable = Table.Buffer( #“Tab Time Lookup Value (timemanager_valor_hora)” ),
GetValue = Table.AddColumn(#“Renamed Columns”, “Valor”, each Table.FirstN(
Table.SelectRows( BufferTable, (BT) => BT[Data inicio] <= [Date] ),
1
){0}[valor]),
#“Changed Type2” = Table.TransformColumnTypes(GetValue,{{“Valor”, type number}})

It’s funny because it shows only 35 on the new column value.
Here’s my lookup table

and this is my fact table

I really hope you can help me to find out what’s wrong with the code.
Thanks a lot

[Melissa]

please share a sample

And notice that your date filter states: smaller or equal to NOT greater than or equal to
BT[Data inicio] <= [Date]

[pedroccamara]

Sample.xlsx (19.1 KB)
Here it goes @Melissa
It is suppose to every dates from fact table above [Data inicio], should be [valor] on the fact table
Thanks a lot for helping me

[Melissa]

Hi @pedroccamara

So with your adjusted logic, I think this should do it.
There were dates out of scope, those will return a null value

let
    Source = Excel.CurrentWorkbook(){[Name="FactTable"]}[Content],
    ChType = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"id_user", Int64.Type}, {"id_cliente", Int64.Type}, {"Date", type date}, {"horas_facturaveis", type logical}, {"Tempo", Int64.Type}, {"Valor", Int64.Type}}),
    BufferedTable = Table.Buffer( Table.Sort( LookupTable,{{"Data inicio", Order.Ascending}} )),
    GetValue = Table.AddColumn( ChType, "Value", each 
        try Table.LastN(
            Table.SelectRows( BufferedTable, (BT)=> BT[Data inicio] <= [Date]),
        1 ){0}[valor] otherwise null, type number
    )
in
    GetValue 

.
Here’s your excel file incl. this query. Sample (1).xlsx (226.1 KB)
I hope this is helpful

[pedroccamara]

Amazing solution @Melissa!
You’re the best
Thank you so much!!!