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
7 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
Anonymous
7 years agoNot applicable
Thank you! That worked well.