Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
@ugurgulluev 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
Thank you! That worked well.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |