Change from a date interval to single dates

[Fabi1208]

Hello everyone! 😊

I have a tricky problem with my data set. In the picture below you see the date "from" and "to". This means, that the employee was e.g. sick from a specific date to a specific date. So, only the timeframe and not the single days are given in the data set. But to analyse the data, I need every specific date of the illness of a single employee.

E.g. "ID" number "10435189" , I need instead of 01.09.2021 to 03.09.2021 something like this:

Do you have an idea or a suggestion for me how to solve my problem?

Thank you very much in advance.

Best regards,

Fabi

[Anonymous · Accepted solution]

Hi @Fabi1208 ,

Could you please provide some error messages to help us clarify your scenario?

I tried @Jakinta 's method and it worked fine on my side.

And I think the most effective way is to use {Number.From() ..Number.From()} to get all list of dates between two dates (From and To columns)

    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each {Number.From([From])..Number.From([To])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type1"

Output:

But anyway ,glad to see your issue has been resolved !

Refer to:

https://www.youtube.com/watch?v=u3HLseZxSj4

https://www.thepoweruser.com/2019/07/23/fill-dates-between-dates-with-power-bi-power-query/

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fabi1208]

Hi Eyelyn9,

thank you very much. This works perfectly. I am not sure why the other solution does not work for me, but this one was exaclty what I was looking for 🙂

Best regards and have a nice weekend,

Fabi

[Jakinta]

You can try with code below in blank query and adjust accordingly.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQ1UtJRMtU30TcyRGaUFJWmKsXqRCsZGpgYmAEFLPSNDSFSCFZaYk4xXJWxKVDEUh8qZalvjKImFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, From = _t, To = _t, #"Half a day" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", type date}, {"To", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Transform( {Number.From([From])..Number.From([To])}, Date.From)),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date")
in
    #"Expanded Date"

[Syndicate_Admin]

Hi Jakinta,

thank you very much for your help and your quick response. I tried your solution out but in the end was not able to handle it. I called the creator of the source and in the end he was able to convert the date collumns into single dates instead of intervals. This solved the problem for me 🙂

Best regards,

Fabi

[Fabi1208]

Hi Jakinta,

thank you very much for your help and your quick response. I tried your solution out but in the end was not able to handle it. I called the creator of the source and in the end he was able to convert the date collumns into single dates instead of intervals. This solved the problem for me 🙂

Best regards,

Fabi