Forum Discussion
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 absence type and person-ID, for every date between Start-Date and End-Date, so the desired result should look as following:
please also find pbix sample file
thanks for your help in advance, kind regards
Franz
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"
9 Replies
Invoking ImkeF
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"Hello Imke, thank you very much, perfect solution again
kind regards
Franz
Hi FranzMei ,
If your datasource is from sql do union operation. so that you can get your output easily
Thanks & Regards,
B V S S
thanks, data source is excel
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 datesThanks & Regards,B V S S
