Translate Excel Formula to Power Query-CodePudding

In my Power Query I have a column that shows different durations on certain items, but it displays an error when attempting to convert on time or duration.

As a solution next to my Excel Table I created a formula that alows to convert the duration in the format I wish to use, but I have not been able to translate the formula into a language that Power Query can understand (I am pretty new to Power Query).

This is how the data is pulled from source:

But I will like it to show like this:

The Excel Formula I am using to accomplish this is:

=IF(LEN([@Age])=7,"0"&[@Age],IF(LEN([@Age])=5,"00:"&[@Age],IF(LEN([@Age])=4,"00:0"&[@Age],IF(LEN([@Age])=3,"00:00"&[@Age],[@Age]))))

It will be nice to have it in the Power Query instead of the Excel sheet, as it serves as a learning oportunity.

I am self learning Power Query in Excel so any help is welcomed.

EDIT: In Case of the duration being more than 24:00:00, how will i approach it

Here is the error code it returns

CodePudding user response：

You can add a custom column with the formula:

Duration.FromText(
            Text.Combine(
                List.LastN(
                    {"00"} & List.ReplaceValue(Text.Split([Age],":"),"","00",Replacer.ReplaceValue),
                        3),
            ":"))

The formula

Splits the text string by the colon into a List
Replacing blanks with {00} and also prepend the list with a {00} element
Retrieve the last three elements and combine them into a colon separated text string.
Use Duration.FromText function to convert to a duration.
Set the data type of the column to duration

In the PQ Editor, a duration will have the format of d.hh:mm:ss, but when you load it back into Excel, you can change that to [hh]:mm:ss

You can accomplish the above all in the PQ User Interface.

Here is M-Code that does the same thing:

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each Duration.FromText(
            Text.Combine(
                List.LastN(
                    {"00"} & List.ReplaceValue(Text.Split([Age],":"),"","00",Replacer.ReplaceValue),
                        3),
            ":"))),
            
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Age"})
in
    #"Removed Columns"

You can even do it (using M-Code in the Advanced Editor) without adding a column by using the Table.TransformColumns function:

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", type text}}),
 
    #"Change to Duration" = Table.TransformColumns(#"Changed Type",
        {"Age", each Duration.FromText(
            Text.Combine(
                List.LastN(
                    {"00"} & List.ReplaceValue(Text.Split(_,":"),"","00",Replacer.ReplaceValue),
                        3),
            ":")), type duration})
in
    #"Change to Duration"

All result in:

Edit
With your modified data, now showing duration values of more than 23 hours (not allowed in a duration literal in PQ), the transformation will be different. We have to check the hours and break it into days and hours if it is more than 23.

Note: the below edit also assumes there will never be anything entered in the day location; and that entries for minutes and seconds will always be within range. If there might be day values, you will need to just add what's there to the "overflow" from the hours entry

So we change the Custom Column formula to check for that:

let 
            split = List.LastN({"00","00"} & List.ReplaceValue(Text.Split([Age],":"),"","00",Replacer.ReplaceValue),4),
            s = Number.From(List.Last(split)),
            m = Number.From(List.LastN(split,2){0}),
            hTotal = Number.From(List.LastN(split,3){0}),
            h = Number.Mod(hTotal,24),
            d = Number.IntegerDivide(hTotal,24)
        in #duration(d,h,m,s)

If you might have illegal values for minutes or seconds, you can add logig to check for that also

Also, if you will be loading this into Excel, and you might have total days >31, you will need to format it (in Excel), as [hh]:mm:ss as with the format d.hh:mm:ss Excel cannot display more than 31 days (although the proper value will be stored in the cell)