none
Query to form a table that adds multiple rows against each entry in a table RRS feed

  • Question

  • Okay so what I wanted to do is to create a table that has a separate row for each month between two dates for every entry in a source table.

    Suppose I have a table like this:

    ID    Name

    1 Car

    2      Lorry

    3      Bike

    and I want to have each one spread across three months of data such that I end up with a table that looks like this

    1      Car       Jan

    2      Car       Feb

    3      Car       Mar

    4      Lorry     Jan

    5      Lorry     Feb

    6      Lorry     Mar

    7      Bike       Jan

    8      Bike       Feb

    9      Bike      Mar

    This would work for me though Ideally I'd love to actually have a start and end date in the source table and have each entry expanded by the difference between these.

    Is this actually possible in power query?  I can do what I need using Excel but it feels like a second step and I'm interested to know if this sort of thing is possible or even usual.

    Many thanks for any suggestions. 

    Friday, January 1, 2016 5:44 PM

Answers

  • This might work:

    let
        // Test source table
        VehicleTypes = #table(
                            {"Id", "Name", "Start Date", "End Date"},
                            {{1, "Car", #date(2015, 1, 1), #date(2016, 2, 1)}, 
                            {2, "Lorry", #date(2016, 1, 1), #date(2016, 4, 1)},
                            {3, "Bike", #date(2016, 1, 1), #date(2016, 6, 1)}}), 
        // Translate start - end date to list of month names
        MonthNames = (start, end) =>
                                if Date.Month(start) <= Date.Month(end) or Date.Year(start) < Date.Year(end) then
                                    {Date.ToText(start, "MMM")} & @MonthNames(Date.AddMonths(start, 1), end)
                                else {},
        // Add month names for each start and end date
        AddMonthNames = (sourceTable) =>
            // Remove start / end date columns
            Table.RemoveColumns(
                // Copy source table row for each generated month
                Table.ExpandListColumn(
                    // Add list of month names for each entry in source table
                    Table.AddColumn(sourceTable, "Month", each MonthNames([#"Start Date"], [#"End Date"])),
                    "Month"),
                {"Start Date", "End Date"})
    in
        AddMonthNames(VehicleTypes)

    The function "MonthNames" translates the given start and end date into a list of month names. The AddMonthNames function in turn takes your source table and adds a new column for each row that contains a list of month names for current row's start and end date. Each row is then copied for every month name in the added column's list. Finally the original "Start Date" and "End Date" columns are removed (no particular reason for this step, I just assumed you might not need those anymore).

    Let me know if that's what you were looking for :)

    • Proposed as answer by Andrin Meier Saturday, January 2, 2016 9:48 AM
    • Marked as answer by Tre4 Saturday, January 2, 2016 10:12 AM
    Friday, January 1, 2016 6:51 PM

All replies

  • This might work:

    let
        // Test source table
        VehicleTypes = #table(
                            {"Id", "Name", "Start Date", "End Date"},
                            {{1, "Car", #date(2015, 1, 1), #date(2016, 2, 1)}, 
                            {2, "Lorry", #date(2016, 1, 1), #date(2016, 4, 1)},
                            {3, "Bike", #date(2016, 1, 1), #date(2016, 6, 1)}}), 
        // Translate start - end date to list of month names
        MonthNames = (start, end) =>
                                if Date.Month(start) <= Date.Month(end) or Date.Year(start) < Date.Year(end) then
                                    {Date.ToText(start, "MMM")} & @MonthNames(Date.AddMonths(start, 1), end)
                                else {},
        // Add month names for each start and end date
        AddMonthNames = (sourceTable) =>
            // Remove start / end date columns
            Table.RemoveColumns(
                // Copy source table row for each generated month
                Table.ExpandListColumn(
                    // Add list of month names for each entry in source table
                    Table.AddColumn(sourceTable, "Month", each MonthNames([#"Start Date"], [#"End Date"])),
                    "Month"),
                {"Start Date", "End Date"})
    in
        AddMonthNames(VehicleTypes)

    The function "MonthNames" translates the given start and end date into a list of month names. The AddMonthNames function in turn takes your source table and adds a new column for each row that contains a list of month names for current row's start and end date. Each row is then copied for every month name in the added column's list. Finally the original "Start Date" and "End Date" columns are removed (no particular reason for this step, I just assumed you might not need those anymore).

    Let me know if that's what you were looking for :)

    • Proposed as answer by Andrin Meier Saturday, January 2, 2016 9:48 AM
    • Marked as answer by Tre4 Saturday, January 2, 2016 10:12 AM
    Friday, January 1, 2016 6:51 PM
  • Wow.  Sir you are a genius many thanks.   This is definitely doing what I was looking to get to.   Thank you so much for an excellent and rapid reply.
    Friday, January 1, 2016 7:09 PM