Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kevin_allen
Frequent Visitor

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)

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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}))})

View solution in original post

2 REPLIES 2
kevin_allen
Frequent Visitor

This was an awesome solution to this issue.  Thank you sir!

wdx223_Daniel
Super User
Super User

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}))})

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors