Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
i am having a duration column name having values in the formate like "9m 10d " in text datatype. I want to show this column values in sort by Desc order. But in report view it is not sorted by desc because of data type , i tried changing the data type in query editor it results error. can anyone help me with this issue by changing the data type.
Hi @LPriyanka,
You can add a numerical sort column, something like:
Table.AddColumn( PrevStepNameHere, "sort duration", each
List.Sum(
List.Transform(
Text.Split([duration], " "), each
if Text.Contains(_, "m")
then Number.From(Text.Select(_, {"0" .. "9"})) * 30
else Number.From(Text.Select(_, {"0" .. "9"}))
)
), Int64.Type
)
Amend to your needs.
I hope this is helpful
can you please help me with eample
Hi @LPriyanka,
Sure, here you go.
Copy this script into a new blank query
let
Source = Table.FromColumns(
{{"9m 1d", "6m 28d", "9m 18d", "10m 4d"}},
type table [#"duration" = text]
),
AddDurationSort = Table.AddColumn( Source, "sort duration", each
List.Sum(
List.Transform( Text.Split([duration], " "), each
if Text.Contains(_, "m")
then Number.From( Text.Select(_, {"0".."9"}))*30
else Number.From( Text.Select(_, {"0".."9"}))
)
), Int64.Type
)
in
AddDurationSort
Cheers