cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User

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

 

 

View solution in original post

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 other members find it more quickly.

View solution in original post

3 REPLIES 3
Super User
Super User

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

 

 

View solution in original post

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 other members 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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 295 members 3,376 guests
Please welcome our newest community members: