Forum Discussion

kevin_allen's avatar
kevin_allen
Frequent Visitor
3 years ago
Solved

Can steps be eliminated?

I have created the following steps in Power Query for one column but need to replicate these steps on 4 additional columns.  Is there a way to combine any of these steps?  This is a duration value(initially text) but many times the duration value is greater than 24 hrs so Power Query fails to read once it is >23:59:59 so I am splitting the column then doing math to get decimal hours.  The final line below is adding up the converted hours to get a decimal number result.  Maybe there is a better solution than what I am attempting here.  Open for suggestions.

 

#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "InstallTimeEa", "InstallTimeEa - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "InstallTimeEa - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"InstallTimeEa - Copy.1", "InstallTimeEa - Copy.2", "InstallTimeEa - Copy.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"InstallTimeEa - Copy.1", type number}, {"InstallTimeEa - Copy.2", type number}, {"InstallTimeEa - Copy.3", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "InstallTimeEa
", each [#"InstallTimeEa - Copy.1"] + [#"InstallTimeEa - Copy.2"]/60 + [#"InstallTimeEa - Copy.3"]/3600)

  • replace all of those steps in one as this

    NewStep=Table.TransformColumns(#"Renamed Columns",{"InstallTimeEa",each List.Sum(List.Transform(List.Zip({Text.Split(_,":"),{1,60,3600}}),each Number.From(_{0})/_{1}))})

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion

    replace all of those steps in one as this

    NewStep=Table.TransformColumns(#"Renamed Columns",{"InstallTimeEa",each List.Sum(List.Transform(List.Zip({Text.Split(_,":"),{1,60,3600}}),each Number.From(_{0})/_{1}))})