Power Query only - Join on Range of Dates

[Anonymous]

 Power Query only 

Hi , i am trying to add to my transaction table , the price from the price list.

transactiontbl:

Customer, Date, Amount

PriceListtbl:

[From Date],[To Date],[Customer],[Price]

Thanks a head,

[v-lili6-msft · Accepted solution]

hi,@Veritas_D

  it is the  M code in the advanced editor in power query of power bi 

you can paste this code in it,

By the way  , these steps in the power query are:

step1.Merge table transactiontbl with table Rates

step2.add a custom column

Step3:

Expand column

Step4:Remove the column and result:

Best Regards,

Lin

[Zubair_Muhammad]

@Anonymous

Could you copy paste few rows of sample data with expected results?

[Anonymous]

[Zubair_Muhammad]

@Anonymous

You can use this

let
    Source = Excel.CurrentWorkbook(){[Name="transactiontbl"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Date", type datetime}, {"Amount", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Customer"},Rates,{"Customer"},"Rates",JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each let mydate= [Date] in 
Table.SelectRows([Rates],each [From Date] <= mydate and [To Date] >= mydate)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Price"}, {"Custom.Price"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Rates"})
in
    #"Removed Columns"

[Anonymous]

Thank you, but i meant power query of power bi

[Zubair_Muhammad]

@Anonymous

Please see attached Excel File for the Steps