Transforming the date range to list

[Anonymous]

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 following structure:

Name	Absence date
John Doe	01/15/2018
John Doe	01/16/2018
John Doe	01/17/2018
John Doe	01/18/2018
Jane Doe	01/25/2018
Jane Doe	01/26/2018
Jane Doe	01/27/2018

Do you guys know a workaround for this?

Thanks in advance.

Ugur

[AnkitBI · Accepted solution]

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]

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

[AnkitBI]

@Anonymous You will need to change from Text format to Date Format using Locale.

Check this Post on how to do it

https://community.powerbi.com/t5/Desktop/How-to-change-the-date-format/td-p/40460

[Anonymous]

Thank you! That worked well.