cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jatin24 Frequent Visitor
Frequent Visitor

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.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: Adding rows in existing table based on logic

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

 

 

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Community Support Team
Community Support Team

Re: Adding rows in existing table based on logic

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]
    )

Capture.PNG

 

Regards,

Frank

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Adding rows in existing table based on logic

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

 

 

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Community Support Team
Community Support Team

Re: Adding rows in existing table based on logic

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]
    )

Capture.PNG

 

Regards,

Frank

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

jatin24 Frequent Visitor
Frequent Visitor

Re: Adding rows in existing table based on logic

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.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors