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