Split Text into multiple columns

[senthil_venkat]

Hi Guys,

I have an data where one specific column "Total Duration" which has the data combined with days, hours and minutes in one field, as shown below.    Is there any way to convert this data to use for calculating actual durations ? or Averages, Total Days, TOtal Hours etc..  or any calculation ? 

Say for ex.. If need to get average of 10 rows, currently with this "Text" type it is not possible for me. I looking for some help to convert this text to values which can used for calculations.  

6 Days 17 Hours 20 Minutes

6 Days 17 Hours 20 Minutes

2 Days 17 Hours 18 Minutes

2 Days 17 Hours 18 Minutes

8 Days 15 Hours 46 Minutes

8 Days 35 Minutes

6 Days 6 Hours 50 Minutes

6 Days 23 Hours 14 Minutes

15 Hours 19 Minutes

15 Hours 19 Minutes

15 Hours 15 Minutes

15 Hours 15 Minutes

2 Days 15 Hours 10 Minutes

2 Days 15 Hours 10 Minutes

1 Day 15 Hours 8 Minutes

1 Day 15 Hours 8 Minutes

15 Hours 8 Minutes

15 Hours 8 Minutes

1 Day 15 Hours 8 Minutes

1 Day 15 Hours 8 Minutes

[mahoneypat · Accepted solution]

Please see the M code below for how to do this by extracting the numbers (from end of input since not all rows have Days value).  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  It results in columns for Days, Hours, and Minutes, along with a Total Duration column, and a total minutes column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlNwSawsVjA0V/DILy0qVjAyUPDNzCstSS1WitUhKG2EJm1oQYq0BVTaFCptYoZN2tgUm4vMoHpMsbrXyBhmowmKNNwqQ0sSxU2JEjdC85Eh9uDCIW0IkkbIWpAgSZowefbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Minutes", each Text.BetweenDelimiters([Column1], " ", " ", {1, RelativePosition.FromEnd}, 0), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Hours", each Text.BetweenDelimiters([Column1], " ", " ", {3, RelativePosition.FromEnd}, 0), type text),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Inserted Text Between Delimiters1", "Days", each Text.BeforeDelimiter([Column1], " ", {4, RelativePosition.FromEnd}), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text Before Delimiter",{{"Minutes", Int64.Type}, {"Hours", Int64.Type}, {"Days", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"Days"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Duration", each #duration([Days]+0,[Hours],[Minutes],0)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Total Minutes", each Duration.TotalMinutes([Duration])),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Total Minutes", Int64.Type}})
in
    #"Changed Type3"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[CNENFRNL · Accepted solution]

My logic is intuitive, first substitute Days/Hours/Minutes with 1440/60/1 respectively; then sum it up.

Pls refer to the following M code,

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"Days","1440",Replacer.ReplaceText,{"Text"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Hours","60",Replacer.ReplaceText,{"Text"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Minutes","1",Replacer.ReplaceText,{"Text"}),
    Custom1 = Table.TransformColumns(#"Replaced Value2", {"Text", each List.Transform(Text.Split(_, " "), Number.From)}),
    Custom2 = Table.TransformColumns(Custom1, {"Text", each
        let
            D = _{0}*_{1},
            h = _{2}?*_{3}?,
            H = if h=null then 0 else h,
            m = _{4}?*_{5}?,
            M = if m=null then 0 else m,
            span = D+H+M
        in
            span
        })
in
    Custom2

[mahoneypat · Accepted solution]

Please see the M code below for how to do this by extracting the numbers (from end of input since not all rows have Days value).  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  It results in columns for Days, Hours, and Minutes, along with a Total Duration column, and a total minutes column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlNwSawsVjA0V/DILy0qVjAyUPDNzCstSS1WitUhKG2EJm1oQYq0BVTaFCptYoZN2tgUm4vMoHpMsbrXyBhmowmKNNwqQ0sSxU2JEjdC85Eh9uDCIW0IkkbIWpAgSZowefbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Minutes", each Text.BetweenDelimiters([Column1], " ", " ", {1, RelativePosition.FromEnd}, 0), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Hours", each Text.BetweenDelimiters([Column1], " ", " ", {3, RelativePosition.FromEnd}, 0), type text),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Inserted Text Between Delimiters1", "Days", each Text.BeforeDelimiter([Column1], " ", {4, RelativePosition.FromEnd}), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text Before Delimiter",{{"Minutes", Int64.Type}, {"Hours", Int64.Type}, {"Days", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"Days"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Duration", each #duration([Days]+0,[Hours],[Minutes],0)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Total Minutes", each Duration.TotalMinutes([Duration])),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Total Minutes", Int64.Type}})
in
    #"Changed Type3"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[senthil_venkat]

Hi Pat,

Wonderful. Thanks for the solution. This works for me. Can you guide me how to replace the example text with actual column from my table ?