Forum Discussion

FranzMei's avatar
FranzMei
Helper I
7 years ago
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...
Need Help
Show and Tell
  • ImkeF's avatar
    ImkeF
    7 years ago

    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"
ImkeF's avatar
ImkeF
Community Champion
7 years ago

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's avatar
    FranzMei
    Helper I
    7 years ago

    Hello Imke, thank you very much, perfect solution again 

    kind regards

    Franz