Grouped Rows Step not working

[vineshparekh]

Hello,

I have this below columns but when I apply "Grouped Rows" step, it shows error because of the format which is in Text.

A/CA/C-TypeMMYYMar24Apr24

A	XYZ	3/1/2024	263:01:00
B	XYZ	4/1/2024		263:01:00
C	XYZ	3/1/2024	281:52:00
D	XYZ	4/1/2024		281:52:00
E	XYZ	3/1/2024	272:25:00
F	XYZ	4/1/2024		272:25:00

The steps;

Previous step: #"Replaced Errors" = Table.ReplaceErrorValues(PivotedCol, {{"Mar24", ""}, {"Apr24", ""}})

Current Step (Not working)

GroupedRows = Table.Group(PivotedCol, {"A/C"}, List.Transform(List.Difference(Table.ColumnNames(PivotedCol), {"MMYY","A/C","A/C-Type"}), (l)=> {l, each List.Sum(Table.Column(_, l)), type nullable number}))
in
GroupedRows

Error: Expression.Error: We cannot apply operator - to types Text and Text.
Details:
Operator=-
Left=263:01:00
Right=263:01:00

Thank you.

[ronrsnfld]

Convert the text values to numbers or to durations.

To convert to a number:

#"Transform Durations" = Table.TransformColumns(#"Previous Step",
        {"Column4", (x)=>let 
            hms = List.Transform(Text.Split(x,":"), each Number.From(_))
                    in  
            hms{0} / 24 + hms{1} / 1440 + hms{2} / 86400, type number})

To convert to a duration type:

#"Transform Durations" = Table.TransformColumns(#"Previous Step",
        {"Column4", (x)=>let 
            hms = List.Transform(Text.Split(x,":"), each Number.From(_))
                    in  
            Duration.From(hms{0} / 24 + hms{1} / 1440 + hms{2} / 86400), type duration})

[vineshparekh]

Hi @ronrsnfld 
Thank you for the formula.

Couple of questions:

1. This formula converts into Days, hours, minutes and seconds. I am looking for only HMS.

2. Can I implement any formula within the group stage so I don't have to create a new step each time when a new column is added. 

3. Is there any other formula (a simpler version) which I can apply so in future if I need to make a change, I can make myself as well?

Thank you.

[ronrsnfld · Accepted solution]

In both Power Query and Power BI, the range of hours supported in a duration type is 0-23.

To display a duration or time value where Hours > 23, you will need to transform the value into text.

As you have discovered, if the value is text, you will not be able to do mathematical operations.

You could write functions to convert your text duration to a real duration for doing the mathematical operation, then another function to convert it back to text for display purposes.

I cannot think of other work arounds.

If that is what you want, then add these two functions in your power query editor:

//fnTextToDuration

(d as text)=>
let 
    hms = List.Transform(Text.Split(d,":"), each Number.From(_))
in 
    Duration.From(hms{0}?/24 + hms{1}?/1440 + hms{2}?/86400)

//fnDurationToHMS

(n as duration)=>
let 
    h = Number.IntegerDivide(Duration.TotalHours(n),1),
    m = Duration.Minutes(n),
    s = Duration.Seconds(n)
in 
    Text.Combine({Text.From(h), Number.ToText(m,"00"), Number.ToText(s,"00")},":")

Given your initial data as I see it:

You could use those functions as shown below.

Note that I am also showing you how to create the aggregation formulas dynamically. You need some kind of formula that will extract the columns to Sum -- In what you posted it seems as if excluding the first three columns will do that. But there might be a different way depending on your actual data:

let

//read in a adjust your data as copy/paste => wrong headers
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYqIjAKSxvqG+kYGRiZAppGZsZWBoZWBAZCtoBSrE63kBFdmglCmgKIUpMwZq2kWhlamRsimueA0Da4UpMwVq2nmRlZGpsimueE0Da40NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"A/CA/C-TypeMMYYMar24Apr24" = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A/CA/C-TypeMMYYMar24Apr24", type text}, {"(blank)", type text}, {"(blank).1", type date}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"(blank)", "A/C-Type"}, {"(blank).1", "MMYY"}, {"(blank).2", "MAR24"}, {"(blank).3", "APR24"}, {"A/CA/C-TypeMMYYMar24Apr24", "A/C"}}),

//create list of columns to sum
//then create the aggregation formula
    
    #"sumCols" = List.RemoveFirstN(Table.ColumnNames(#"Renamed Columns"),3),
    
    #"Aggregate Formula" = List.Transform(#"sumCols", each {_, (t)=> 
        fnDurationToHMS(
        List.Sum(
            List.Transform(
                Table.Column(t,_), (li)=> fnTextToDuration(li)))), type text}),
    
    Group = Table.Group(#"Renamed Columns", "A/C-Type",
        #"Aggregate Formula")
in
    Group

and this results in: