Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Power Query only - Join on Range of Dates

 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,

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi,@Veritas_D

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

11.PNG

you can paste this code in it,

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

step1.Merge table transactiontbl with table Rates

12.PNG

step2.add a custom column

13.PNG

Step3:

Expand column

14.PNG

Step4:Remove the column and result:

15.PNG

Best Regards,

Lin

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

View solution in original post

6 REPLIES 6
v-lili6-msft
Community Support
Community Support

hi,@Veritas_D

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

11.PNG

you can paste this code in it,

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

step1.Merge table transactiontbl with table Rates

12.PNG

step2.add a custom column

13.PNG

Step3:

Expand column

14.PNG

Step4:Remove the column and result:

15.PNG

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

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


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

image.png

@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"

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Thank you, but i meant power query of power bi

@Anonymous

 

Please see attached Excel File for the Steps

 

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.