Search code examples
powerquerym

How to "Merge" a table of dates with exclusive employee names by day

I have two tables, one with dates and another one with names and what I'd like to do is "merge" this two tables into another one like the example bellow assining for each day only one person repeating after the last one (F).

tables

Tks

Solution

  • Based on this setup

    Table

    Table2

    Read each step name:

    let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    ExtractDates = Table.AddColumn(Origen, "SingleDate", each Text.BeforeDelimiter([Date], " "), type text),
    ChangeTypes = Table.TransformColumnTypes(ExtractDates,{{"SingleDate", type date}}),
    AddDayOfWeek = Table.AddColumn(ChangeTypes, "Día de la semana", each Date.DayOfWeek([SingleDate]), Int64.Type),
    FilterWeekends = Table.SelectRows(AddDayOfWeek, each ([Día de la semana] <> 0 and [Día de la semana] <> 6)),
    AddIndex = Table.AddIndexColumn(FilterWeekends, "Índice", 0, 1, Int64.Type),
    RestartIndexEverySixRows = Table.TransformColumns(AddIndex,{{"Índice", (index) => Number.Mod(index, 6) + 1, type number}}),
    MergeTablePerson = Table.NestedJoin(RestartIndexEverySixRows, {"Índice"}, TablePerson, {"ID"}, "TableLetter", JoinKind.LeftOuter),
    ExpandName = Table.ExpandTableColumn(MergeTablePerson, "TableLetter", {"Name"}, {"Name"}),
    RemoveCols = Table.RemoveColumns(ExpandName,{"Día de la semana", "Índice"}),
    MergeExpandName = Table.NestedJoin(ExtractDates, {"Date"}, ExpandName, {"Date"}, "Se expandió TableLetter", JoinKind.LeftOuter),
    ExpandName2 = Table.ExpandTableColumn(MergeExpandName, "Se expandió TableLetter", {"Name"}, {"Name"}),
    OrderByDate = Table.Sort(ExpandName2,{{"SingleDate", Order.Ascending}}),
    RemoveCol = Table.RemoveColumns(OrderByDate,{"SingleDate"})
in
    RemoveCol