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:

 

NameStart dateEnd date
John Doe01/15/201801/18/2018
Jane Doe01/25/201801/27/2018

 

to the following structure:

 

NameAbsence date
John Doe01/15/2018
John Doe01/16/2018
John Doe01/17/2018
John Doe01/18/2018
Jane Doe01/25/2018
Jane Doe01/26/2018
Jane Doe01/27/2018

 

Do you guys know a workaround for this?

 

Thanks in advance.

Ugur

Need Help
Show and Tell
  • AnkitBI's avatar
    AnkitBI
    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.

     

4 Replies

  • AnkitBI's avatar
    AnkitBI
    Icon for Solution Sage rankSolution Sage
    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello AJ,

       

      Thanks for the solution!

       

      When I use the query it gives the error:

       

      data format.error we couldn't parse the input provided as a Date value

       

      Details: 01/15/2018

       

      Most probably because my date format is dd/mm/yyyy in the source data, but I couldn't figure out how to fix.

       

      Best regards,

      Ugur