cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RichardFig1
Helper I
Helper I

Tratamiento campo texto como duración

Espero puedan ayudarme. Tengo una base de datos que para cada transacción, me indica la duración de un ticket de la Mesa de ayuda en dos columnas:
   - Días transcurridos: Es la cantidad de días que tiene el ticket en cada grupo resolutor. este puede ser 1 día, 2 días, etc.
   - Tiempo x estatus: Complementa la columna anterior al indicar la cantidad de horas, minutos y segundos (adicionales a los días transcurridos) que tiene el ticket en cada grupo resolutor. Este dato viene en formato texto, sin delimitación, como se observa en la siguiente imagen:

RichardFig1_0-1632780777849.png

Los últimos 2 dígitos corresponden a los segundos, los siguientes 2 dígitos corresponden a los minutos y los siguientes 2 dígitos a las horas.

 

Por lo tanto, la interpretación de estas dos columnas, se observa en la columna G.

 

Necesito crear una columna que me indique la duración del ticket en cada grupo resolutor en horas.

 

Según el proveedor, el campo Tiempo x estatus viene en hh:mm:ss (hora:minuto:segundo), pero Power BI no lo interpreta como "duración". En el Power Query trato de cambiar el dato a duración, pero no lo interpreta correctamente (ver siguiente imagen).

RichardFig1_1-1632780894029.png

 

Qué me pueden recomendar para, al menos, lograr tratar la columna "Tiempo x estatus" como duración u horas para poder sumarla con la columna "Días transcurridos"?

 

Saludos,

Richard

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlfSUTI0sTA1VorViVYyg/JMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dias transcurridos" = _t, #"Tiempo x estatus" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each #duration(Int32.From([Dias transcurridos]),
Int32.From(Text.Start([Tiempo x estatus],Text.Length([Tiempo x estatus])-4)),
Int32.From(Text.Start(Text.End([Tiempo x estatus],4),2)),
Int32.From(Text.End([Tiempo x estatus],2)))),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}})
in
    #"Changed Type"

 

See attached.

View solution in original post

v-angzheng-msft
Community Support
Community Support

Hi, @RichardFig1 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZI7jsMwDETv4joG+BflqwTpUqRJlfsDa2plm9yNG2EE6WlmzPt9aRCfG/tyW7Cv0FYCwhC0gh+ixYa48r5Cez43wNdrE3+/N+XPZ3ncDpR1o/0Q8Qp23CZNwiZKA2UVpQXF4n260tNVFjpRgQStKCsoFxwBOQeUJCQ2FCFWkAOluKNACooceaIuV1nwdBWdAVdXLaMQDEbAlppGzyLKA9DwDhgo2lEAgfKCIrbx7t50P29nIRNFZ8ALRQWFjUehnFGSxezKTldXV39q52E9Rul77faLojoMA0Vau3Li+f/PekiSOObKv8xV6Qpa7+O9lowUMQOS/A9IYxgePw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Fecha creacion" = _t, #" Fecha Modif" = _t, #"Dias transcurridos" = _t, #"Tiempo x estatus" = _t, #"Interpretacion duracion" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Fecha creacion", type text}, {" Fecha Modif", type text}, {"Dias transcurridos", Int64.Type}, {"Tiempo x estatus", Int64.Type}, {"Interpretacion duracion", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Fecha creacion", type date}, {" Fecha Modif", type date}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each Text.Range(Text.Remove([Interpretacion duracion],{"a".."z",":"}),0,2)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Range(Text.Remove([Interpretacion duracion],{"a".."z",":"}),2,2)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Text.Range(Text.Remove([Interpretacion duracion],{"a".."z",":"}),4,2)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Text.Range(Text.Remove([Interpretacion duracion],{"a".."z",":"}),6,2)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Custom", Int64.Type}, {"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type1", "Custom.4", each #duration([Custom],[Custom.1],[Custom.2],[Custom.3])),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.5", each #duration(0,[Custom.1],[Custom.2],[Custom.3])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom5",{{"Custom", "d"}, {"Custom.1", "h"}, {"Custom.2", "m"}, {"Custom.3", "s"}, {"Custom.4", "d:h:m:s"}, {"Custom.5", "h:m:s"}}),
    #"Added Custom6" = Table.AddColumn(#"Renamed Columns", "Custom", each [#"h:m:s"]+#duration([Dias transcurridos],0,0,0)),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom6",{{"Custom", "or h:m:s + day"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"d", "h", "m", "s"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"d:h:m:s", type duration}, {"h:m:s", type duration}, {"or h:m:s + day", type duration}})
in
    #"Changed Type2"

Result:

vangzhengmsft_0-1632975810729.png

 

 

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-angzheng-msft
Community Support
Community Support

Hi, @RichardFig1 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZI7jsMwDETv4joG+BflqwTpUqRJlfsDa2plm9yNG2EE6WlmzPt9aRCfG/tyW7Cv0FYCwhC0gh+ixYa48r5Cez43wNdrE3+/N+XPZ3ncDpR1o/0Q8Qp23CZNwiZKA2UVpQXF4n260tNVFjpRgQStKCsoFxwBOQeUJCQ2FCFWkAOluKNACooceaIuV1nwdBWdAVdXLaMQDEbAlppGzyLKA9DwDhgo2lEAgfKCIrbx7t50P29nIRNFZ8ALRQWFjUehnFGSxezKTldXV39q52E9Rul77faLojoMA0Vau3Li+f/PekiSOObKv8xV6Qpa7+O9lowUMQOS/A9IYxgePw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Fecha creacion" = _t, #" Fecha Modif" = _t, #"Dias transcurridos" = _t, #"Tiempo x estatus" = _t, #"Interpretacion duracion" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Fecha creacion", type text}, {" Fecha Modif", type text}, {"Dias transcurridos", Int64.Type}, {"Tiempo x estatus", Int64.Type}, {"Interpretacion duracion", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Fecha creacion", type date}, {" Fecha Modif", type date}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each Text.Range(Text.Remove([Interpretacion duracion],{"a".."z",":"}),0,2)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Range(Text.Remove([Interpretacion duracion],{"a".."z",":"}),2,2)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Text.Range(Text.Remove([Interpretacion duracion],{"a".."z",":"}),4,2)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Text.Range(Text.Remove([Interpretacion duracion],{"a".."z",":"}),6,2)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Custom", Int64.Type}, {"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type1", "Custom.4", each #duration([Custom],[Custom.1],[Custom.2],[Custom.3])),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.5", each #duration(0,[Custom.1],[Custom.2],[Custom.3])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom5",{{"Custom", "d"}, {"Custom.1", "h"}, {"Custom.2", "m"}, {"Custom.3", "s"}, {"Custom.4", "d:h:m:s"}, {"Custom.5", "h:m:s"}}),
    #"Added Custom6" = Table.AddColumn(#"Renamed Columns", "Custom", each [#"h:m:s"]+#duration([Dias transcurridos],0,0,0)),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom6",{{"Custom", "or h:m:s + day"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"d", "h", "m", "s"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"d:h:m:s", type duration}, {"h:m:s", type duration}, {"or h:m:s + day", type duration}})
in
    #"Changed Type2"

Result:

vangzhengmsft_0-1632975810729.png

 

 

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

lbendlin
Super User
Super User

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlfSUTI0sTA1VorViVYyg/JMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dias transcurridos" = _t, #"Tiempo x estatus" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each #duration(Int32.From([Dias transcurridos]),
Int32.From(Text.Start([Tiempo x estatus],Text.Length([Tiempo x estatus])-4)),
Int32.From(Text.Start(Text.End([Tiempo x estatus],4),2)),
Int32.From(Text.End([Tiempo x estatus],2)))),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}})
in
    #"Changed Type"

 

See attached.

View solution in original post

Muchas gracias @ibendlin


Hice el proceso recomendado, pero no me funcionó. Este proceso cambió los datos en general, aunque los deja en formato duración.
RichardFig1_1-1632937990888.png

Saludos,

Richard

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!