Forum Discussion
Anonymous
7 years agoNot applicable
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...
- 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.
AnkitBI
Solution Sage
7 years agoAnonymous 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
7 years agoNot applicable
Thank you! That worked well.