Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Adding rows in existing table based on logic

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.

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    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

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    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's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Hi Anonymous 

     

    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(_))

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.