Replacing Substitute from Excel

Power Query
1

Hello, I successfully replaced the words for 20 days 15 hours 4 minutes 32 seconds
to attempt to apply the functions Durations.Totalhours(DateList) & Durations.Totalminutes(), which expects 4 items in the list. How do I read in the column as a list so the Duration functions work?

SubDatetime.pbix (12.2 KB) SubDatetime.xlsx (11.8 KB)
Excel List
="(" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2," days “,”,")," hours “,”,")," minutes “,”,")," seconds",")")," hour “,”,")," minute “,”,")," second",")")," day “,”,")

Expression.Error: We cannot convert a value of type List to type Function.
Details:
Value=[List]
Type=[Type]

2

Hi @Ackrite55,

I extracted the parentheses from the DateList column, split this column by delimiter, added a duration value and then passed that through Duration.TotalDays and Duration.TotalHours.

Here’s what the full Query looks like now:

let
    Source = Excel.Workbook(File.Contents(FileLocation), null, true),
    Time3_Table = Source{[Item="Time3",Kind="Table"]}[Data],
    #"Confirm Text Format List" = Table.TransformColumnTypes(Time3_Table,{{"Name", type text}, {"Begin", type any}, {"End", type any}, {"DateList", type text}, {"TimeList", type text}, {"Avg. Duration", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Confirm Text Format List","(","",Replacer.ReplaceText,{"DateList", "TimeList"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",")","",Replacer.ReplaceText,{"DateList", "TimeList"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "DateList", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"DateList.1", "DateList.2", "DateList.3", "DateList.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DateList.1", Int64.Type}, {"DateList.2", Int64.Type}, {"DateList.3", Int64.Type}, {"DateList.4", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each #duration([DateList.1],[DateList.2],[DateList.3],[DateList.4])),
    #"Inserted Total Days" = Table.AddColumn(#"Added Custom", "Total Days", each Duration.TotalDays([Duration]), type number),
    #"Inserted Total Hours" = Table.AddColumn(#"Inserted Total Days", "Total Hours", each Duration.TotalHours([Duration]), type number),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Total Hours",{"Name", "Begin", "End", "TimeList", "Avg. Duration", "Duration", "Total Days", "Total Hours"})
in
    #"Removed Other Columns"

and this is the result:

image
image.png866×319 17.2 KB

I hope this is helpful. Note. If you change the FileLocation parameter the query will be restored.
SubDatetime.pbix (12.8 KB)

2 Likes
3

Amazing! Very helpful. Thank you.