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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
LeeA
New Member

Look up and match values from a table containing date ranges with another date range fact table

Hi,

 

Ive got two tables. One containing position changes of an employee and another with the salary changes. I need to match the periods and obtain the salary changes for each position. Ive Included the tables and the expected results below. Would really appreciate if someone can help me on the Power querry solution for this.

@amitchandak , @SpartaBI @Ashish_Mathur @DataInsights 

LeeA_4-1653620005581.png

LeeA_5-1653620044333.png

 

Regards,

Lee.

 

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8klNVdJRckxOzi/NK0nMKwFxnJyBpFdeEZA0MNQFIiMDIwMgx9hA18AEwonViVYKz8zJoaZeX0eQ3mC4XlOYXiDLwBxDb0BiZVF+To6Cf1paZnJqEboBZnA9OkpGJroGllADYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Position = _t, Company = _t, Lvl = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Position", type text}, {"Company", type text}, {"Lvl", type text}, {"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([Start Date])..Number.From([End Date])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Employee", "Date"}, #"Employee salary changes", {"Employee", "Date"}, "Employee salary changes", JoinKind.LeftOuter),
    #"Expanded Employee salary changes" = Table.ExpandTableColumn(#"Merged Queries", "Employee salary changes", {"Start Date", "End Date", "Sal"}, {"Start Date_Sal", "End Date-Sal", "Sal"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Employee salary changes",{"Date"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Employee", "Position", "Company", "Lvl", "Start Date", "End Date", "Sal"}, {{"Salary start date", each List.Min([Start Date_Sal]), type nullable date}, {"Salary end date", each List.Max([#"End Date-Sal"]), type nullable date}}),
    #"Inserted Count" = Table.AddColumn(#"Grouped Rows", "Sal start", each List.Max({[Start Date], [Salary start date]}), Int64.Type),
    #"Inserted Count1" = Table.AddColumn(#"Inserted Count", "Sal end", each List.Min({[End Date], [Salary end date]}), Int64.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Count1",{"Salary start date", "Salary end date"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Sal start", type date}, {"Sal end", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Employee", "Position", "Company", "Lvl", "Start Date", "End Date", "Sal start", "Sal end", "Sal"})
in
    #"Reordered Columns"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
LeeA
New Member

Thanks heaps @Ashish_Mathur . awesome !! It works. The secret is to generate a list between the start date and end date and match based on that field. 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8klNVdJRckxOzi/NK0nMKwFxnJyBpFdeEZA0MNQFIiMDIwMgx9hA18AEwonViVYKz8zJoaZeX0eQ3mC4XlOYXiDLwBxDb0BiZVF+To6Cf1paZnJqEboBZnA9OkpGJroGllADYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Position = _t, Company = _t, Lvl = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Position", type text}, {"Company", type text}, {"Lvl", type text}, {"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([Start Date])..Number.From([End Date])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Employee", "Date"}, #"Employee salary changes", {"Employee", "Date"}, "Employee salary changes", JoinKind.LeftOuter),
    #"Expanded Employee salary changes" = Table.ExpandTableColumn(#"Merged Queries", "Employee salary changes", {"Start Date", "End Date", "Sal"}, {"Start Date_Sal", "End Date-Sal", "Sal"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Employee salary changes",{"Date"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Employee", "Position", "Company", "Lvl", "Start Date", "End Date", "Sal"}, {{"Salary start date", each List.Min([Start Date_Sal]), type nullable date}, {"Salary end date", each List.Max([#"End Date-Sal"]), type nullable date}}),
    #"Inserted Count" = Table.AddColumn(#"Grouped Rows", "Sal start", each List.Max({[Start Date], [Salary start date]}), Int64.Type),
    #"Inserted Count1" = Table.AddColumn(#"Inserted Count", "Sal end", each List.Min({[End Date], [Salary end date]}), Int64.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Count1",{"Salary start date", "Salary end date"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Sal start", type date}, {"Sal end", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Employee", "Position", "Company", "Lvl", "Start Date", "End Date", "Sal start", "Sal end", "Sal"})
in
    #"Reordered Columns"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors