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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
phauschke
Frequent Visitor

List.dates between dates in same column

Hi,

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
ABC100001.01.2022
ABC150001.01.2023
ABC200001.02.2023
XYZ10001.01.2022
XYZ20001.01.2023

Thx PH

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @phauschke 

 

You can try this:

let
    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")
in
    #"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. 

 

Result:

vjingzhang_0-1675389093424.png

 

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

View solution in original post

10 REPLIES 10
v-jingzhang
Community Support
Community Support

Hi @phauschke 

 

You can try this:

let
    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")
in
    #"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. 

 

Result:

vjingzhang_0-1675389093424.png

 

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.

Hello,
I have a similar issue but instead of the start date, I have the end date. See below. I also have several other columns. 

Could you please assist?

bandrade_0-1711653119394.png

 

Thanks,

Bruna

Hi @bandrade,

  1. could you be more specific please? Would you like to generate dates between end dates for same Plnt?
  2. provide sample data as table so we can copy/paste (see note below my post if you don't know how to do it) - or upload dummy data i.e. to google drive and provide a link (with public permissions
  3. provide expected result based on sample data.

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hello,

 

I want to generate days between end dates for each work center. Please note that the "day" column corresponds to a weekday but does not have a fixed value. We cannot assume that "1" is correspondent to the same week day on every instace.

 

You can find the sample data here.

 

Thanks,

B.

 

Why don't you provided expected result? I don't know what do you mean by "days" if you want to generate dates between lowest and highest date for work center or what exactly? Provide at least screenshot of expected result corresponding to sample data please.

 

If you want to generate same column as your column [Day], you can find solution here:

 

Result

dufoq3_0-1711720857244.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndXBbsIwEATQf/EZhL1ZO91zT1VLVYn2hPj/3yhItM66oZnZQ4DDjBI9Bc/5nDRLTrv0VuT6WQ7lIFn09vN65fv1dco1XXZ/wnUZnm7fuSFx4eITF1cuXqF4+4m33/hd5mktXoSj6XnMpucxnJ7HdHoe4ykT6aOkj5I+Svoo6aOkT137X60mjZQxUsZIGSNljJORzL050jifnsd8eh7z6XnMp+dBnxnzOZa2fzl+JPgQXxb8QS4PH2q1I4HOFOhooFPxjj/hBYOWANxw1rMlgm449dkSgTec/6CeRvQ0oqcRPY3oaURvcx1c2iJsFmGzCJtF2CzANkwH9tIN+wHeqQX0hiVhS4TesClgaQb1Pt9f96fnBA/LIu/e61IfPtdaRfjKxFeUr1S44iblXtkSFp7MDwrbwdH8nLAdnM2PCeimATcNuGnATQNuGnDbnJFl2AJgFgCzAJgFwIwH8wOCvWh+P8D7NN7Nrwfbwd38doCd+V+3yzc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Plnt = _t, #"Work ctr" = _t, #"End Date" = _t, Day = _t, #"No." = _t, #"Cap." = _t, Shift = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"End Date", type date}}, "en-US"),
    #"Inserted Day of Week" = Table.AddColumn(#"Changed Type", "Day of Week", each Date.DayOfWeek([End Date], Day.Monday)+1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Day of Week", {"Work ctr", "End Date"}, {{"All", each Table.AddIndexColumn(_, "Day Generated", if _{0}[Day of Week] = 6 then 6 else 1, 1, Int64.Type), type table}}),
    #"Combined All" = Table.Combine(#"Grouped Rows"[All]),
    #"Removed Columns" = Table.RemoveColumns(#"Combined All",{"Day of Week"})
in
    #"Removed Columns"

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hello, 

please see the expected results. I'm looking for the valid day column.

bandrade_0-1711728568815.png

 

Thanks,

B.

Like this?

 

Result:

dufoq3_0-1711791802927.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndXBbsIwEATQf/EZhL1ZO91zT1VLVYn2hPj/3yhItM66oZnZQ4DDjBI9Bc/5nDRLTrv0VuT6WQ7lIFn09vN65fv1dco1XXZ/wnUZnm7fuSFx4eITF1cuXqF4+4m33/hd5mktXoSj6XnMpucxnJ7HdHoe4ykT6aOkj5I+Svoo6aOkT137X60mjZQxUsZIGSNljJORzL050jifnsd8eh7z6XnMp+dBnxnzOZa2fzl+JPgQXxb8QS4PH2q1I4HOFOhooFPxjj/hBYOWANxw1rMlgm449dkSgTec/6CeRvQ0oqcRPY3oaURvcx1c2iJsFmGzCJtF2CzANkwH9tIN+wHeqQX0hiVhS4TesClgaQb1Pt9f96fnBA/LIu/e61IfPtdaRfjKxFeUr1S44iblXtkSFp7MDwrbwdH8nLAdnM2PCeimATcNuGnATQNuGnDbnJFl2AJgFgCzAJgFwIwH8wOCvWh+P8D7NN7Nrwfbwd38doCd+V+3yzc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Plnt = _t, #"Work ctr" = _t, #"End Date" = _t, Day = _t, #"No." = _t, #"Cap." = _t, Shift = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"End Date", type date}}, "en-US"),
    // Added [Valid Date] into inner [All] table
    GroupedRows = Table.Group(ChangedType, {"Work ctr"}, {{"All", each 
        [ a = List.Dates([End Date]{0}, List.Count([End Date]), #duration(1,0,0,0)),
          b = Table.FromColumns(Table.ToColumns(_) & {a}, Value.Type(_ & #table(type table[Valid Date=date], {{}})))
        ][b], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Yes!!! Thank you so much 😊

You're welcome @bandrade.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors