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 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

Need Help
Tips and Tricks

5 Replies

  • Jakinta's avatar
    Jakinta
    Icon for Solution Sage rankSolution 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"
    • Fabi1208's avatar
      Fabi1208
      Frequent Visitor
      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

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

    • Fabi1208's avatar
      Fabi1208
      Frequent Visitor
      4 years ago

      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