transfer table holiday plan

[FranzMei]

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

sample file

thanks for your help in advance, kind regards

Franz

[Anonymous]

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

[FranzMei]

Hi, thank you for your effort, i already have a solution

kind regards Franz

[Anonymous]

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

[FranzMei]

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 

[Anonymous]

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

[FranzMei]

thanks, data source is excel

[Greg_Deckler]

Invoking @ImkeF 

[ImkeF · Accepted solution]

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"

[FranzMei]

Hello Imke, thank you very much, perfect solution again 

kind regards

Franz