Forum Discussion
Transforming the date range to list
- 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.
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.
- Anonymous7 years agoNot applicable
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
- AnkitBI7 years agoSolution Sage
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
- Anonymous7 years agoNot applicable
Thank you! That worked well.