Split single table rows into N number of rows based on "duration" field

[lpriceFTW]

I have a project table in which each record contains start/end dates, durations, etc.

In order to visualize and analyze the data better accross days/months, I need to split the single project records into multiple rows - one row per project-day.

For instance, here is some example data:

Project Name	Start	End	Duration (Days)
Project A	1/30/24	2/2/24	3

By splitting the single project record into multiple rows (one per elapsed calendar day), the new table would look like this:

Project Name	Start	End	Duration (Days)	Date
Project A	1/30/24	2/2/24	3	1/30/24
Project A	1/30/24	2/2/24	3	1/31/24
Project A	1/30/24	2/2/24	3	2/1/24
Project A	1/30/24	2/2/24	3	2/2/24

Obviously, the real dataset has a vast number of projects, and those projects last much longer than 3 days.

How would I go about doing this in Power Query M?

[dufoq3]

Hi @lpriceFTW, another solution.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRMtQ3NtA3MgGyjPSNIAxjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Start = _t, End = _t, #"Duration (Days)" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}, {"Duration (Days)", Int64.Type}}, "en-US"),
    Ad_Dates = Table.AddColumn(ChangedType, "Date", each List.Dates([Start], Duration.TotalDays([End]-[Start])+1, #duration(1,0,0,0)), type list),
    ExpandedDates = Table.ExpandListColumn(Ad_Dates, "Date"),
    ChangedType2 = Table.TransformColumnTypes(ExpandedDates,{{"Date", type date}})
in
    ChangedType2

[lpriceFTW]

@wdx223_Daniel thanks for the reply!

In trying to apply your solution, I am encountering the following expression error:

"Expression.Error: We cannot apply field access to the type List."

Here is my code:

let
    Source = Table.SelectColumns(DataModel,{"Start Date Used","End Date Used"}),
    CreateDurationColumn = Table.AddColumn(Source, "Duration Days", each Text.BeforeDelimiter(Duration.ToText([End Date Used] - [Start Date Used]),".")),
    ChangeDurationType = Table.TransformColumnTypes(CreateDurationColumn,{{"Duration Days", Int64.Type}}),
    KeepFirstRows = Table.FirstN(ChangeDurationType,3),


    ExpandProjectRows = #table( //THIS IS THE TRANSFORMATION STEP
        
        // Headers
        Table.ColumnNames(KeepFirstRows)&{"Date"},

        // Rows
        List.TransformMany(
            Table.ToRows(KeepFirstRows),
            each List.Dates(
                [Start Date Used],
                [Duration Days],
                Duration.From(1)
            ),
            (x,y)=>x&{y}
        )
    )

in
    ExpandProjectRows

This is what the table "KeepFirstRows" looks like, before applying your transformation at step "ExpandProjectRows":

Any idea what is going wrong?

[ronrsnfld · Accepted solution]

You are using the List.TransformMany function incorrectly.

The arguments should be referring to items in the lists generated by the Table.ToRows function; not to items in the original table.

And I would do it a bit differently

• Your example shows that the dates are inclusive, so you need to add one (1) to the date subtraction to obtain the actual duration.
• I would set the data types of the expanded table explicitly in the function

let
    Source = Table.SelectColumns(Table,{"Start","End"}),
    #"Add Duration Column" = Table.AddColumn(Source, "Days", each Duration.Days([End]-[Start])+1, Int64.Type),
    
    #"Expand" = #table(type table[Start=date, End=date, Days=Int64.Type, Date=date],
        List.TransformMany(
            Table.ToRows(#"Add Duration Column"),
            each List.Dates(_{0},_{2}, #duration(1,0,0,0)),
            (x,y)=>x&{y}))
in
    #"Expand"

[lpriceFTW]

Pardon my ignorance; I have been researching my questions for about an hour now and reading through Microsoft's documentation, but am not able to understand the second and third argument in your List.TransformMany() function.

I am confused as to what "_{0},_{2}" is doing. Why the underscores? And I assume {0} and {2} are column references to Start and Duration, yes?

I also do not understand what "#duration(1,0,0,0)" does. I understand the "#" symbol to reference existing tables and records, but not sure what it is doing here.

And then "(x,y)=>x&{y})". Microsoft's documentation's example does not use the anonymous function format with 'x' and 'y', so I am not sure how this is working.

Apologies; I am sure my misunderstanding is due to my ignorance of the PowerQuery M language's nuances.

And thanks for the tips on making the previous steps more efficient.

[ronrsnfld]

each ... _{0},_{2}

The underscore (_) represents each List item from the first argument. That List item is a List of the row values (Table.ToRows). So the {0} and {2} are the indexes to the Start and Duration values for that row.

If you read the "About" paragraph in the MS documentation for that function, you will see the siignature explanation for x and y (which can be called any variable, by the way).

#duration(...) syntax you can also find in the MS Power Query documentation. The arguments are days, hours, minutes, seconds

[lpriceFTW]

Thank you @ronrsnfld for the explanations and assistance on this.

For the edification of anyone who sees this later, I will post my working code below. Keep in mind that I added some lines to make the index values dynamic based on column name:

let
    // Get the required columns from source query
    Source = Table.SelectColumns(DataModel,{"Job Type","Well Name","Start Date Used","End Date Used"}),

    // Calculate and add duration column
    ChangeDurationType = Table.AddColumn(Source, "Project Duration", each Duration.Days([End Date Used] - [Start Date Used]) + 1,Int64.Type),

    // Return a table - use the transformMany function to split out the rows
    ExpandProjectRows = #table(
        
        // Headers
        Table.ColumnNames(ChangeDurationType)&{"Individual Dates"},

        // Rows
        let
            // Make the row indexes dynamic
            idxStart = Table.Schema(ChangeDurationType){[Name="Start Date Used"]}[Position],
            idxDuration = Table.Schema(ChangeDurationType){[Name="Project Duration"]}[Position],

            // Perform the transformation and return the rows
            columnRows = List.TransformMany(
                Table.ToRows(ChangeDurationType),
                each List.Dates(_{idxStart}, _{idxDuration}, #duration(1,0,0,0)),
                (x,y)=>x&{y}
            )
        in
            // Final set of rows to be used in constructing table
            columnRows
    ),

    // Define each column type
    #"Changed Type" = Table.TransformColumnTypes(ExpandProjectRows,{{"End Date Used", type date}, {"Project Duration", Int64.Type}, {"Individual Dates", type date}, {"Start Date Used", type date}, {"Well Name", type text}})

in
    #"Changed Type"

[lpriceFTW]

I still do not fully understand how its working, but after correcting an error in another part of my Query, your solution worked as expected!

Thank you!

[wdx223_Daniel]

NewStep=#table(Table.ColumnNames(YourTable)&{"Date"},List.TransformMany(Table.ToRows(YourTable),each List.Dates(_{1},_{3},Duration.From(1)),(x,y)=>x&{y}))