Forum Discussion
Solved
transfer table holiday plan
Hello, i am struggeling with following issue, i have a table containg a Person-ID, Start-Date, End-Date and Absence type which i need to transfer in query-editor, resulting in one row for each abs...
Hi FranzMei ,
You can add a column wiht a list of all the dates in between and then expand that list like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lEyMlDwKs2pVDAyMLQAcY3gXEsg11spVgeu3AgoYGCKIm9ggVs52HQzFHk0bkAosnpjkHmGCo6l6aXFJXALTNAEMLQYmqF4wBDTilgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t, Startdate = _t, Enddate = _t, #"Absence Type" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Startdate", type date}, {"Enddate", type date}, {"Absence Type", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([Startdate], Number.From([Enddate]-[Startdate])+1, #duration(1,0,0,0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom") in #"Expanded Custom"
Hi FranzMei ,
Please check and confirm about your data. You are asking all dates between Start and End Dates but in your desired out put image it is showing only three rows.. But Actually there is nearly one year difference between those two dates. Can you eloberate little more. Because i tried some thing it is giving all the dates between two dates
Thanks & Regards,
B V S S
Hi, thank you for your effort, i already have a solution
kind regards Franz
Hi FranzMei ,
Can you please share pbix file. Because i also wants to know... I tried that Mquery but my files is not loading it's giving error. So Please share your sample pbix file
Thank you in advance
Hello, i have updated the file with solution, ans also the excel with basic data.
the query with working solution is the working one.
kind regards Franz