Merged Query Issue

[mdmilner]

Merged queries do not seem to be doing what I'd expect.
What I want are all dates within a given date range and then when I have matching values in the second table, it populates those columns (left outer)
What I'm getting are results that would match an inner join.

BaselineDates is just a single column date table which in this case has 31 values (1/1/2025 -> 1/31/2025).
I'm curious what I'm missing.

let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"BaselineDates", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"BaselineDates"}, #"EIS OT DATA", {"Overtime Date"}, "EIS OT DATA", JoinKind.RightOuter),
#"Expanded EIS OT DATA" = Table.ExpandTableColumn(#"Merged Queries", "EIS OT DATA", {"EMP NO", "Hours Counted"}, {"EMP NO", "Hours Counted"}),
#"Grouped Rows" = Table.Group(#"Expanded EIS OT DATA", {"BaselineDates", "EMP NO"}, {{"SumOT", each List.Sum([Hours Counted]), type nullable number}})
in
#"Grouped Rows"

 

[arunsjain]

Hi,

Try this
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"BaselineDates"}, #"EIS OT DATA", {"Overtime Date"}, "EIS OT DATA", JoinKind.LeftOuter),