cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Creating multiple date tables in one table

Hey guys,

 

I have this certain thing I'm trying to create, but I lack the skills for it. It's hard to exlain in the title, so Ill try to be as clear as possible with my data below:

 

My input data looks like this:

Phase is just a code for a project. Start and end mark the start and end date of said phase.

ErikBI_0-1593688183375.png

 

 

 

What I'm trying to achieve is a date table that looks like this, where a row is created for each day between the start and end per phase. So for phase A I'd have 356 rows created for each day of the year, and after that it goes on to phase B where it creates a row for each day between 1-5-2019 and 31-12-2019, etc.

ErikBI_1-1593688191850.png

 

 

I recently learned how to create a date table, but this phase thing adds an element that is beyond my skill level. I hope some someone knows how to achieve this, because it will help me a lot!

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Creating multiple date tables in one table

Hi , @ErikBI 

Click New source ->create  a blank query->advanced Editor

Then paste following M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjC0BDGN9I2hnFidaCUnoJAppqyRAVjWGbteqKwLUMhc38gIJm2qb2SJkHXFK+uGJovmLHe8mj3wynrilfXCK+uNWzYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Phase = _t, start = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Phase", type text}, {"start", type date}, {"end", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Duration.TotalDays(Date.StartOfDay([end])-Date.StartOfDay([start]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Generate(()=>[Custom], each _ >= 0, each _ - 1)),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Date", each if [Custom.1]=[Custom] then [start] else  Date.AddDays(Date.StartOfDay([start]),[Custom]-[Custom.1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"start", "end", "Custom", "Custom.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Phase", Order.Ascending}, {"Date", Order.Ascending}})
in
    #"Sorted Rows"

 For more detail, please double click the step  in "APPLIED STEPS".

90.png

 

sample pbix attached

 

Best Regards,
Community Support Team _ Eason
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
Highlighted
Super User IX
Super User IX

Re: Creating multiple date tables in one table

@ErikBI ,

see if one of these two can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

Or Open Pbix

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Creating multiple date tables in one table

Hi , @ErikBI 

Click New source ->create  a blank query->advanced Editor

Then paste following M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjC0BDGN9I2hnFidaCUnoJAppqyRAVjWGbteqKwLUMhc38gIJm2qb2SJkHXFK+uGJovmLHe8mj3wynrilfXCK+uNWzYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Phase = _t, start = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Phase", type text}, {"start", type date}, {"end", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Duration.TotalDays(Date.StartOfDay([end])-Date.StartOfDay([start]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Generate(()=>[Custom], each _ >= 0, each _ - 1)),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Date", each if [Custom.1]=[Custom] then [start] else  Date.AddDays(Date.StartOfDay([start]),[Custom]-[Custom.1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"start", "end", "Custom", "Custom.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Phase", Order.Ascending}, {"Date", Order.Ascending}})
in
    #"Sorted Rows"

 For more detail, please double click the step  in "APPLIED STEPS".

90.png

 

sample pbix attached

 

Best Regards,
Community Support Team _ Eason
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

Highlighted
Helper III
Helper III

Re: Creating multiple date tables in one table

Brilliant! it took a bit of fiddling with the advanced editor to adjust the source data, but it seems to work perfectly now.

 

Thanks!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors