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

[LeeA]

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 

Regards,

Lee.

[LeeA]

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. 

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

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.