Forum Discussion

Fabi1208's avatar
Fabi1208
Frequent Visitor
4 years ago
Solved

Change from a date interval to single dates

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 ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Jakinta's avatar
Jakinta
Solution Sage
4 years ago

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's avatar
Syndicate_Admin
Administrator
4 years ago

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