I have the following two tables.

and the following one:

Both tables are in two different Excel files.

What I want to achieve is to add an extra column to the second one with the relevant Year taken from the first one. For "relevant year" I mean the year of each invoice that fits between the start date and end date of the second table.

The result I want to achieve is the following:

I know I can add an extra column with an M formula that calculates the year without the need to have the first table but this way it sounds more maintainable. Next year I have just to add a line to the second Excel table and the year in the third table will be correct instead of amending the formula.

How can I do that?

CodePudding user response：

Start your date table with 1/1/1980 or similar, not year 00

Pull in the data from separate query datetable, add a custom column with formula

{Number.From([Start Date])..Number.From([End Date])}

and expand it to rows and covert to date. That gives every date combination. Left outer merge that into your other table

Sample code you can apply to your invoice table if the other data is already loaded into query named datetable

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(datetable, "Custom", each {Number.From([Start Date])..Number.From([End Date])}),      // assume other data is in query named: datetable
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"DateChanged Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Date"}, #"DateChanged Type", {"Custom"}, "Table3", JoinKind.LeftOuter),
#"Expanded Table3" = Table.ExpandTableColumn(#"Merged Queries", "Table3", {"Year"}, {"Year"})
in #"Expanded Table3"

CodePudding user response：

Just because... Here's another approach:

I named your first table Table1 and your second table Table2 for this.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Start Date", each List.Max(Table.SelectRows(Table2, let dt = [Date] in each ([Start Date] < #date(Date.Year(dt), Date.Month(dt), Date.Day(dt))))[Start Date]), type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End Date", each List.Min(Table.SelectRows(Table2, let dt = [Date] in each ([End Date] > #date(Date.Year(dt), Date.Month(dt), Date.Day(dt))))[End Date]), type date),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Year", each Table.SelectRows(Table2, let sdt = [Start Date], edt = [End Date] in each ([Start Date] = #date(Date.Year(sdt), Date.Month(sdt), Date.Day(sdt)) and [End Date] = #date(Date.Year(edt), Date.Month(edt), Date.Day(edt)))){0}[Year]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Invoice", "Date", "Year"})
in
    #"Removed Other Columns"