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,
I'm wondering if it is possible to transform a table with tasks and start and end date to a per day overview that shows how many task i have to do on one day. To make it more clear I added the following example:
Input:
Task | Duration | Start Date | End Date |
Task 1 | 2 | 20.07.2021 | 22.07.2021 |
Task 2 | 3 | 22.07.2021 | 25.07.2021 |
Task 3 | 2 | 21.07.2021 | 23.07.2021 |
Task 4 | 1 | 24.07.2021 | 25.07.2021 |
Task 5 | 3 | 20.07.2021 | 23.07.2021 |
Task 6 | 2 | 24.07.2021 | 26.07.2021 |
Task 7 | 3 | 26.07.2021 | 29.07.2021 |
Task 8 | 2 | 29.07.2021 | 31.07.2021 |
Output:
Date | Amount of Tasks per day |
20.07.2021 | 2 |
21.07.2021 | 3 |
22.07.2021 | 4 |
23.07.2021 | 3 |
24.07.2021 | 3 |
25.07.2021 | 3 |
26.07.2021 | 2 |
27.07.2021 | 1 |
28.07.2021 | 1 |
29.07.2021 | 2 |
30.07.2021 | 1 |
31.07.2021 | 1 |
Thank you for you help.
Solved! Go to Solution.
Yes @THU - see this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRMgJhAz0Dcz0jAyOwgBGcE6sDVQhSZIwqB+SYYio0hploiKzQGFOhCVAcLGdCwERTmNUGBEw0g1mNYqIZpkJzmIlmyAotMRVawEy0RFJojPBZbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Duration = _t, #"Start Date " = _t, #"End Date" = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start Date ", type date}, {"End Date", type date}}, "en-PG"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Duration", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date Range", each {Number.From([#"Start Date "])..Number.From([End Date])}),
#"Expanded Date Range" = Table.ExpandListColumn(#"Added Custom", "Date Range"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date Range",{{"Date Range", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Date Range"}, {{"Tasks per Day", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
It turns this:
into this:
I created a new column called Date Range that is a list of the dates from Start Date to End Date, then expanded that list into rows, and finally grouped by that date and counted the rows (tasks). See below on how to copy that code to a blank query, but the crux of the logic is this list:
={Number.From([#"Start Date "])..Number.From([End Date])}
Then I expanded that column to "New Rows" and changed it back to a date format. List ranges cannot contain dates, but you might recognize those numbers - they are the same "dates" Excel shows when it isn't formatted as a date.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you exactly what I needed.
Great @THU - glad I was able to assist.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes @THU - see this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRMgJhAz0Dcz0jAyOwgBGcE6sDVQhSZIwqB+SYYio0hploiKzQGFOhCVAcLGdCwERTmNUGBEw0g1mNYqIZpkJzmIlmyAotMRVawEy0RFJojPBZbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Duration = _t, #"Start Date " = _t, #"End Date" = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start Date ", type date}, {"End Date", type date}}, "en-PG"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Duration", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date Range", each {Number.From([#"Start Date "])..Number.From([End Date])}),
#"Expanded Date Range" = Table.ExpandListColumn(#"Added Custom", "Date Range"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date Range",{{"Date Range", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Date Range"}, {{"Tasks per Day", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
It turns this:
into this:
I created a new column called Date Range that is a list of the dates from Start Date to End Date, then expanded that list into rows, and finally grouped by that date and counted the rows (tasks). See below on how to copy that code to a blank query, but the crux of the logic is this list:
={Number.From([#"Start Date "])..Number.From([End Date])}
Then I expanded that column to "New Rows" and changed it back to a date format. List ranges cannot contain dates, but you might recognize those numbers - they are the same "dates" Excel shows when it isn't formatted as a date.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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.