Group by start and end date or join multiple columns in Power Query

Question

I have an employees table with mutations to their contracts

EmpID Start      End         Function Hours SalesPercentage
1     01-01-2020 31-12-2020  FO Desk  40    1
1     01-01-2020 31-01-2021  FO Desk  32    1
1     01-02-2021             FO Desk  32    0.50
2     01-01-2021 31-01-2021  BO       32    0
2     01-02-2021             BO/FO    32    .25

For dynamic calculation of the amount of emplyees and their sales percentages I need to turn this into a tabel with an entry per month:

Year Month EmpID Hours SalesPercentage
2020 1     1     40    1
2020 2     1     40    1
..
2020 12    1     40    1
2021 1     1     32    1
2021 1     2     32    0
2021 2     1     32    0.50
2021 2     2     32    0.25

I have a simple Year Month table that I would like to append the mutation data to, but joining on multiple columns is not possible as far as I can tell. Is there a way around this?

Answer 1

Try this below

It generates a list of all year/month combinations for each row, then expands it and removes extra columns

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{ {"Start", type date}, {"End", type date}}),
#"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "newcol",
    each 
    let
        begin = Date.StartOfMonth([Start]),
        End2 = if [End] = null then [Start] else [End]
    in
        List.Accumulate(
            {0..(Date.Year(End2)-Date.Year([Start]))*12+(Date.Month(End2)-Date.Month([Start]))},
            {},
            (s,c) => s&{Date.AddMonths(begin,c)}
        )
),
#"Expanded newcol" = Table.ExpandListColumn(#"Added Custom", "newcol"),
#"Added Custom2" = Table.AddColumn(#"Expanded newcol", "Year", each Date.Year([newcol])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Month", each Date.Month([newcol])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Start", "End", "Function", "newcol"})
in  #"Removed Columns"