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 Community,
New to Power BI and need your help.
I have got a requirement to separate the project start and end dates from a project dates column.
Project Name Status Dates
Project1 | Completed | Jan 15-21, 2019 |
Project2 | Completed | Feb 1-9, 2020 |
Project Name Status Start Date End Date
Project1 | Completed | Jan 15, 2019 | Jan 21, 2019 |
Project2 | Completed | Feb 1, 2020 | Feb 9, 2020 |
I tried creating a custom column using REPLACE and CONTAINS functions but no luck.
Cheers
Solved! Go to Solution.
Hi @vabiuser1
How is your Dates field look like if start and end dates in differents monthes?
If you have preiods mandatory from the same month you can try a calculated columns like
Start Date =
var _y = RIGHT([Dates],4)
var _m = LEFT([Dates],3)
var _delimiterPos = SEARCH("-",[Dates])
var _d = MID([Dates],4,_delimiterPos-4)
RETURN
DATEVALUE(CONCATENATE(_d,CONCATENATE(" ",CONCATENATE(_m,CONCATENATE(" ",_y)))))
and
End Date =
var _y = RIGHT([Dates],4)
var _m = LEFT([Dates],3)
var _delimiterPos = SEARCH("-",[Dates])
var _d = MID([Dates],_delimiterPos+1,LEN([Dates])-6-_delimiterPos)
RETURN
DATEVALUE(CONCATENATE(_d,CONCATENATE(" ",CONCATENATE(_m,CONCATENATE(" ",_y)))))
Hi @vabiuser1 ,
You also could use M code(Edit Queries) to achieve this goal
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLjFU0lFyzs8tyEktSU0Bsr0S8xQMTXWNDHUUjAwMLZVideBKjdCUuqUmKRjqWoIUGhkoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Status " = _t, Dates = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Status ", type text}, {"Dates", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Dates", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Dates.1", "Dates.2", "Dates.3"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Dates.2", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Dates.2.1", "Dates.2.2"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter1", "start", each [Dates.1]& " "&[Dates.2.1]
&", "&[Dates.3]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "end", each [Dates.1]& " "&[Dates.2.2]&" "
&[Dates.3]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Dates.1", "Dates.2.1", "Dates.2.2", "Dates.3"})
in
#"Removed Columns1"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @vabiuser1 ,
You also could use M code(Edit Queries) to achieve this goal
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLjFU0lFyzs8tyEktSU0Bsr0S8xQMTXWNDHUUjAwMLZVideBKjdCUuqUmKRjqWoIUGhkoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Status " = _t, Dates = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Status ", type text}, {"Dates", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Dates", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Dates.1", "Dates.2", "Dates.3"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Dates.2", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Dates.2.1", "Dates.2.2"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter1", "start", each [Dates.1]& " "&[Dates.2.1]
&", "&[Dates.3]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "end", each [Dates.1]& " "&[Dates.2.2]&" "
&[Dates.3]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Dates.1", "Dates.2.1", "Dates.2.2", "Dates.3"})
in
#"Removed Columns1"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @vabiuser1
How is your Dates field look like if start and end dates in differents monthes?
If you have preiods mandatory from the same month you can try a calculated columns like
Start Date =
var _y = RIGHT([Dates],4)
var _m = LEFT([Dates],3)
var _delimiterPos = SEARCH("-",[Dates])
var _d = MID([Dates],4,_delimiterPos-4)
RETURN
DATEVALUE(CONCATENATE(_d,CONCATENATE(" ",CONCATENATE(_m,CONCATENATE(" ",_y)))))
and
End Date =
var _y = RIGHT([Dates],4)
var _m = LEFT([Dates],3)
var _delimiterPos = SEARCH("-",[Dates])
var _d = MID([Dates],_delimiterPos+1,LEN([Dates])-6-_delimiterPos)
RETURN
DATEVALUE(CONCATENATE(_d,CONCATENATE(" ",CONCATENATE(_m,CONCATENATE(" ",_y)))))
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 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |