Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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"
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.