Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
from service now I get a date in the column task:latable_busienss_duration in the following format: "10 days 3 hous 10 minutes", I would like to split this column so that I will have a column with only days, a column with only hours and a column with only minutes.
here is a screenshot of the data:
Can you provide me some hint?
Thank you
Kind regards
Marta
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSIT0msLI43js/ILy0qjgfyczPzSktSi5VidaKVTCCypqYoooYg0XhDsBawAFw3VJlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Elapsed = _t]),
#"Removed Plural" = Table.AddColumn(Source, "a", each Text.Remove([Elapsed], "s")),
Mapping = Table.TransformColumns(#"Removed Plural", {"a", each let l = List.Zip(List.Split(Text.Split(_, "_"),2)) in Record.FromList(l{0}, l{1})}),
#"Expanded Elapsed" = Table.ExpandRecordColumn(Mapping, "a", {"day", "hour", "minute"}, {"day", "hour", "minute"})
in
#"Expanded Elapsed"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
thank you , it worked 🙂
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSIT0msLI43js/ILy0qjgfyczPzSktSi5VidaKVTCCypqYoooYg0XhDsBawAFw3VJlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Elapsed = _t]),
#"Removed Plural" = Table.AddColumn(Source, "a", each Text.Remove([Elapsed], "s")),
Mapping = Table.TransformColumns(#"Removed Plural", {"a", each let l = List.Zip(List.Split(Text.Split(_, "_"),2)) in Record.FromList(l{0}, l{1})}),
#"Expanded Elapsed" = Table.ExpandRecordColumn(Mapping, "a", {"day", "hour", "minute"}, {"day", "hour", "minute"})
in
#"Expanded Elapsed"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |