cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jatin24
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
Community Support

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
Community Support

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

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors