Forum Discussion
Post ProdigyExpand Dates to complete ranges
Hi all!
I have a transactional table that is displays when a case was opened and when it was closed.
| Case | Date Start | Date Closed |
| AAA1 | 01.01.2022 | 01.01.2023 |
| AA2 | 05.02.2022 | 12.03.2022 |
| AA21 | 06.07.2022 |
Now I would like to expand this table in a way that I get a row for each date betweend Date Start and Date closed.
If there is no Date Closed defined, then it should be expanded to today.
Example
| Case | Date |
| AA2 | 05.02.2022 |
| AA2 | 06.02.2022 |
| AA2 | 07.02.2022 |
| AA2 | ... |
| AA2 | 12.03.2022 |
How would yo do that in Power Query?
Hi joshua1990
You can add a custom column with below code, then expand the list column to new rows.
let _startDate = [Date Start], _endDate = if [Date Closed] = null then Date.From(DateTime.LocalNow()) else [Date Closed] in List.Generate(()=>[x=_startDate], each [x]<=_endDate, each [x=Date.AddDays([x],1)], each [x])let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0NFTSUTIyMDLSM9SDM42BzFgdsLQRXNpIz8gMyFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Case = _t, #"Date Start" = _t, #"Date Closed" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case", type text}, {"Date Start", type date}, {"Date Closed", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let _startDate = [Date Start], _endDate = if [Date Closed] = null then Date.From(DateTime.LocalNow()) else [Date Closed] in List.Generate(()=>[x=_startDate], each [x]<=_endDate, each [x=Date.AddDays([x],1)], each [x])), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom") in #"Expanded Custom"You can also add a custom column with below code then expand it to new rows.
List.Dates([Date Start], Duration.TotalDays((if [Date Closed] = null then Date.From(DateTime.LocalNow()) else [Date Closed]) - [Date Start]) + 1, #duration(1,0,0,0))Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
3 Replies
v-jingzhangCommunity Support
Hi joshua1990
You can add a custom column with below code, then expand the list column to new rows.
let _startDate = [Date Start], _endDate = if [Date Closed] = null then Date.From(DateTime.LocalNow()) else [Date Closed] in List.Generate(()=>[x=_startDate], each [x]<=_endDate, each [x=Date.AddDays([x],1)], each [x])let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0NFTSUTIyMDLSM9SDM42BzFgdsLQRXNpIz8gMyFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Case = _t, #"Date Start" = _t, #"Date Closed" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case", type text}, {"Date Start", type date}, {"Date Closed", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let _startDate = [Date Start], _endDate = if [Date Closed] = null then Date.From(DateTime.LocalNow()) else [Date Closed] in List.Generate(()=>[x=_startDate], each [x]<=_endDate, each [x=Date.AddDays([x],1)], each [x])), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom") in #"Expanded Custom"You can also add a custom column with below code then expand it to new rows.
List.Dates([Date Start], Duration.TotalDays((if [Date Closed] = null then Date.From(DateTime.LocalNow()) else [Date Closed]) - [Date Start]) + 1, #duration(1,0,0,0))Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!- ImkeF
Community Champion
Hi joshua1990 ,
this function does the job: Date.DatesBetween to retrieve dates between 2 dates in Power BI and Power Query – The BIccountant - wdx223_Daniel
=#table({"Case","Date"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Dates(_{1},Duration.Days(_{2}-_{1}),Duration.From(1)),(x,y)=>{x{0},y}))
