Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

List.dates between dates in same column


sorry I cannot find solution for generating dates between dates in same column.

e.g. Valid from is valid till another row is created for its department.

Dept.Hrs rateValid from

Thx PH


You are genius. Thx a lot. Just one remark for others if they will want to use it, as first precondition it have to be correctly sorted. Otherwise it create a lot of duplicates.

View solution in original post

Community Support
Community Support

Hi @phauschke 


You can try this:

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjQwMABSBoZ6QGRkYGSkFKsDlzNFlTNGkjNC6DNCyEVERkHMxDQSImWEYWIsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dept." = _t, #"Hrs rate" = _t, #"Valid from" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept.", type text}, {"Hrs rate", Int64.Type}, {"Valid from", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Dept.", "Index"}, #"Added Index1", {"Dept.", "Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Valid from"}, {"Added Index1.Valid from"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Dept.", Order.Ascending}, {"Valid from", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Added Index1.Valid from", "Valid to"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,Date.From(DateTime.LocalNow()),Replacer.ReplaceValue,{"Valid to"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Valid date", each List.Dates([Valid from], Duration.Days([Valid to] - [Valid from]), #duration(1,0,0,0))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Dept.", "Hrs rate", "Valid date"}),
    #"Expanded Valid date" = Table.ExpandListColumn(#"Removed Other Columns", "Valid date")
    #"Expanded Valid date"


Steps are:

1. Add two Index columns: one is starting from 1 with step 1 and the other is starting from 0 with step 1. 

2. Merge the query with itself based on Dept. and Index columns. Expand the result table column and select only "valid from" to expand. Rename this column to "valid to".

3. Sort rows by "Dept." and "Valid from" columns ascendingly. 

4. Replace null values in "valid to" column to today's date. 

5. Add a custom column with List.Dates to get dates between "valid from" and "valid to".

6. Remove unnecessary columns. 

7. Expand the date list column generated from step 5. 





Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

You are genius. Thx a lot. Just one remark for others if they will want to use it, as first precondition it have to be correctly sorted. Otherwise it create a lot of duplicates.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors