Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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(_))
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
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.
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
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(_))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |