Latest Enterprise DNA Initiatives

Merge if meet condition or M code

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

@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
1 Like

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.

Best regards
Gifted

2 Likes

@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
3 Likes

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

2 Likes

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
Tab Time Lookup Value

and this is my fact table
Tab Time

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

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]

1 Like

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

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

2 Likes

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