Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Transforming the date range to list

Hi,   I am trying to convert the following table for the absence dates:   Name Start date End date John Doe 01/15/2018 01/18/2018 Jane Doe 01/25/2018 01/27/2018   to the follo...
  • AnkitBI's avatar
    7 years ago

    Try using below in Power Query. Source is using Enter Data. Primary function used is {Number.From([Start Date])..Number.From([End Date]) }

     

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lEyMNQ3NNU3MjC0gHIsIJxYHXRlRsjKjMyhymIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Start Date" = _t, #"End Date" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start Date", type date}, {"End Date", type date}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Start Date])..Number.From([End Date]) }),
        #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Start Date", "End Date"})
    in
        #"Removed Columns"

    Output is as per your required.

     

     

    Regards
    AJ
    Do Like Post if response seems good and Worth liking.
    Do Mark as Solution if response resolved your Issue.