Adding rows in existing table based on logic

[jatin24]

Hi, 

Im trying to find a way i can get PowerBI automatically add new rows to an existing table based on logic.

The table has a project start date and end date and a value for hours per week. I need to add rows for each week for each project along with the hours/week value which is avaliable already.

Existing table:

Project    StartDate       EndDate            Date      Hours/week
proj1      Jan 7 2019      Feb 3 2019                         15
proj2      Jan 21 2019    Feb 10 2019                       20

New table:

Project     StartDate     EndDate              Date              Hours/week
proj1       Jan 7 2019    Feb 3 2019         Jan 7 2019          15
proj1       Jan 7 2019    Feb 3 2019         Jan 14 2019        15
proj1       Jan 7 2019    Feb 3 2019         Jan 21 2019        15
proj1       Jan 7 2019    Feb 3 2019         Jan 28 2019        15

proj2       Jan 21 2019   Feb 10 2019      Jan 21 2019        20
proj2       Jan 21 2019   Feb 10 2019      Jan 28 2019        20
proj2       Jan 21 2019   Feb 10 2019      Feb 4 2019         20

Can this be done? 

Thank you.

[Zubair_Muhammad · Accepted solution]

Hi @jatin24 

You can add this Custom Column and then expand it to new rows

Please see attached file's Query editor as well

=List.Transform(List.Numbers(Number.From([StartDate]),Number.RoundUp((Number.From([EndDate])-Number.From([StartDate]))/7),7),each Date.From(_))

[jatin24]

Hi Zubair and Frank

Both of your soultions worked like a charm! Thanks a lot for your help. Its amazing how powerful DAX can be if used properly.

[v-frfei-msft · Accepted solution]

Hi @jatin24 ,

To create a calculated table.

Table = 
VAR k =
    UNION ( VALUES ( Table1[EndDate] ), VALUES ( Table1[StartDate] ) )
VAR _date =
    ADDCOLUMNS (
        CALENDAR ( MINX ( k, 'Table1'[EndDate] ), MAXX ( k, 'Table1'[EndDate] ) ),
        "week", WEEKDAY ( [Date], 1 )
    )
VAR _table =
    FILTER ( _date, [week] = 2 )
VAR fil =
    ADDCOLUMNS (
        CROSSJOIN ( _table, Table1 ),
        "start", Table1[StartDate],
        "end", Table1[EndDate],
        "Date1", [Date]
    )
RETURN
    SELECTCOLUMNS (
        FILTER ( fil, [start] <= [Date1] && [end] >= [Date1] ),
        "Date2", [Date],
        "Project1", Table1[Project],
        "Start_date", Table1[StartDate],
        "End_date", Table1[EndDate],
        "Hours_week", Table1[Hours/Week]
    )

Regards,

Frank

Regards,

Frank

[Zubair_Muhammad · Accepted solution]

Hi @jatin24 

You can add this Custom Column and then expand it to new rows

Please see attached file's Query editor as well

=List.Transform(List.Numbers(Number.From([StartDate]),Number.RoundUp((Number.From([EndDate])-Number.From([StartDate]))/7),7),each Date.From(_))