Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Solved! Go to Solution.
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}))})
This was an awesome solution to this issue. Thank you sir!
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}))})