Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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.
Solved! Go to 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.
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.
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.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.