Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
THU
Regular Visitor

Transform Data to a per day view

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:

TaskDurationStart Date End Date
Task 1220.07.202122.07.2021
Task 2322.07.202125.07.2021
Task 3221.07.202123.07.2021
Task 4124.07.202125.07.2021
Task 5320.07.202123.07.2021
Task 6224.07.202126.07.2021
Task 7326.07.202129.07.2021
Task 8229.07.202131.07.2021

 

Output: 

DateAmount of Tasks per day
20.07.20212
21.07.20213
22.07.20214
23.07.20213
24.07.20213
25.07.20213
26.07.20212
27.07.20211
28.07.20211
29.07.20212
30.07.20211
31.07.20211

 

Thank you for you help. 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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:

edhans_0-1626798694166.png

into this:

edhans_1-1626798720256.png

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
THU
Regular Visitor

Thank you exactly what I needed. 

Great @THU - glad I was able to assist.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

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:

edhans_0-1626798694166.png

into this:

edhans_1-1626798720256.png

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors