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
Syndicate_Admin
Administrator
Administrator

Cómo extraer un número de una cadena

Hola Queridos,

Quiero extraer algunos números de una cadena para un collumn, he intentado usar algunas fórmulas con Len, Righ, Left y Search, pero no conseguiqué un buen resultado con él.

mi ejemplo es:

ejemplocuerdaPeríodo 1Período 2Período 3Período 4
152.50#50#54;54.00#54#54;54.00#54#54;54.00#54#5454545454
20.00#0#0;0.00#0#0;37.67#21#46;46.00#46#46004646
30.00#0#0;0.00#0#0;46.00#36#56;56.00#56#56005656
4##;##;93.33#86#97;96.00#96#96009796

Como se ve arriba a tengo un collumn llamado como String y quiero extraer el último número antes de ";" (punto y coma) de cada cadena. Para cada cadena hay 4 punto y coma, por lo que tendremos 4 collumn con los números para cada período.

¿Podrías ayudarme por favor?

2 ACCEPTED SOLUTIONS
Syndicate_Admin
Administrator
Administrator

Esta es una solución que usa la interfaz de Power Query.

Haga clic con el botón derecho en la columna de cadena y en dividir por delimitador (use ;

Seleccione la 1ª columna (el resultado de la división)-> Vaya al menú 'Agregar columna' >en la sección Texto,

elija Extract->Text After Delimiter ->use # , y 'From the End of the Input' y 0 (en la sección avanzada).

Eso creará una nueva columna con solo el texto después del último #.

Puede hacerlo para cada columna y, a continuación, quitar las columnas innecesarias, ordenar los datos (reemplace blanks/null por 0) y establecer los tipos de datos correctos.

Déjame cómo te pones

View solution in original post

Syndicate_Admin
Administrator
Administrator

No @leandross89,

Al igual que @HotChilli mencionado, puede cumplir sus requisitos en el Editor de Power Query y el código detallado es el siguiente:

Método 1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fYtLCsAgDETvkmwlqPlYyVHE+1+jiYWuSplZPIZ5a0GDAtpJK2bFVagm/DDssqCHV3OK+As8yAb2hmIulqtY9Bj8aTwvNlRzPazJx5AwED06mZjxMpzD53nN4HjtGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Example = _t, String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Example", Int64.Type}, {"String", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "String", "String - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "String - Copy", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"String - Copy.1", "String - Copy.2", "String - Copy.3", "String - Copy.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"String - Copy.1", type text}, {"String - Copy.2", type text}, {"String - Copy.3", type text}, {"String - Copy.4", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "String - Copy.1", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"String - Copy.1.1", "String - Copy.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"String - Copy.1.1", type text}, {"String - Copy.1.2", Int64.Type}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "String - Copy.2", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"String - Copy.2.1", "String - Copy.2.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"String - Copy.2.1", type text}, {"String - Copy.2.2", Int64.Type}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "String - Copy.3", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"String - Copy.3.1", "String - Copy.3.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"String - Copy.3.1", type text}, {"String - Copy.3.2", Int64.Type}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type4", "String - Copy.4", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"String - Copy.4.1", "String - Copy.4.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"String - Copy.4.1", type text}, {"String - Copy.4.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type5",{"String - Copy.1.1", "String - Copy.2.1", "String - Copy.3.1", "String - Copy.4.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"String - Copy.1.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"String - Copy.2.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"String - Copy.1.2", "Period 1"}, {"String - Copy.2.2", "Period 2"}, {"String - Copy.3.2", "Period 3"}, {"String - Copy.4.2", "Period 4"}})
in
    #"Renamed Columns"

method1.PNG

Método 2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fYtLCsAgDETvkmwlqPlYyVHE+1+jiYWuSplZPIZ5a0GDAtpJK2bFVagm/DDssqCHV3OK+As8yAb2hmIulqtY9Bj8aTwvNlRzPazJx5AwED06mZjxMpzD53nN4HjtGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Example = _t, String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Example", Int64.Type}, {"String", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "String", "String - Copy"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"String - Copy", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "String - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"String - Copy", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "String - Copy", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"String - Copy.1", "String - Copy.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"String - Copy.1", type text}, {"String - Copy.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"String - Copy.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"String - Copy.2"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Example", "String"}, {{"all", each _, type table [Example=nullable number, String=nullable text, #"String - Copy.2"=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Period", each Table.Column([all],"String - Copy.2")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Period", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Extracted Values", "Period", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Period.1", "Period.2", "Period.3", "Period.4"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Period.1", Int64.Type}, {"Period.2", Int64.Type}, {"Period.3", Int64.Type}, {"Period.4", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type3",{"all"})
in
    #"Removed Columns1"

method2.PNG

Por supuesto, también puede usar DAX:

Period 1 = 
VAR SplitbySemicolon_1 =
    LEFT ( [String], SEARCH ( ";", [String], 1, 0 ) - 1 )
VAR Period_ =
    TRIM (
        RIGHT (
            SUBSTITUTE ( SplitbySemicolon_1, "#", REPT ( " ", LEN ( SplitbySemicolon_1 ) ) ),
            LEN ( SplitbySemicolon_1 )
        )
    )
RETURN
    IF ( Period_ = "", 0, CONVERT ( Period_, DOUBLE ) )
Period 2 = 
VAR SplitbySemicolon_1 =
    LEFT ( [String], SEARCH ( ";", [String], 1, 0 ) - 1 )
VAR Rest_1 =
    RIGHT ( [String], LEN ( [String] ) - LEN ( SplitbySemicolon_1 ) - 1 )
VAR SplitbySemicolon_2 =
    LEFT ( Rest_1, SEARCH ( ";", Rest_1, 1, 0 ) - 1 )
VAR Period_ =
    TRIM (
        RIGHT (
            SUBSTITUTE ( SplitbySemicolon_2, "#", REPT ( " ", LEN ( SplitbySemicolon_2 ) ) ),
            LEN ( SplitbySemicolon_2 )
        )
    )
RETURN
    IF ( Period_ = "", 0, CONVERT ( Period_, DOUBLE ) )
Period 3 = 
VAR SplitbySemicolon_1 =
    LEFT ( [String], SEARCH ( ";", [String], 1, 0 ) - 1 )
VAR Rest_1 =
    RIGHT ( [String], LEN ( [String] ) - LEN ( SplitbySemicolon_1 ) - 1 )
VAR SplitbySemicolon_2 =
    LEFT ( Rest_1, SEARCH ( ";", Rest_1, 1, 0 ) - 1 )
VAR Rest_2 =
    RIGHT ( Rest_1, LEN ( Rest_1 ) - LEN ( SplitbySemicolon_2 ) - 1 )
VAR SplitbySemicolon_3 =
    LEFT ( Rest_2, SEARCH ( ";", Rest_2, 1, 0 ) - 1 )
VAR Period_ =
    TRIM (
        RIGHT (
            SUBSTITUTE ( SplitbySemicolon_3, "#", REPT ( " ", LEN ( SplitbySemicolon_3 ) ) ),
            LEN ( SplitbySemicolon_3 )
        )
    )
RETURN
    IF ( Period_ = "", 0, CONVERT ( Period_, DOUBLE ) )
Period 4 = 
VAR Period_ =
    TRIM (
        RIGHT (
            SUBSTITUTE ( [String], "#", REPT ( " ", LEN ( [String] ) ) ),
            LEN ( [String] )
        )
    )
RETURN
    IF ( Period_ = "", 0, CONVERT ( Period_, DOUBLE ) )

method3.PNG

Para obtener más detalles, consulte el archivo .pbix adjunto.

Saludos

Icey

Si este post ayuda,entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

View solution in original post

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

No @leandross89,

Al igual que @HotChilli mencionado, puede cumplir sus requisitos en el Editor de Power Query y el código detallado es el siguiente:

Método 1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fYtLCsAgDETvkmwlqPlYyVHE+1+jiYWuSplZPIZ5a0GDAtpJK2bFVagm/DDssqCHV3OK+As8yAb2hmIulqtY9Bj8aTwvNlRzPazJx5AwED06mZjxMpzD53nN4HjtGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Example = _t, String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Example", Int64.Type}, {"String", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "String", "String - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "String - Copy", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"String - Copy.1", "String - Copy.2", "String - Copy.3", "String - Copy.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"String - Copy.1", type text}, {"String - Copy.2", type text}, {"String - Copy.3", type text}, {"String - Copy.4", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "String - Copy.1", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"String - Copy.1.1", "String - Copy.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"String - Copy.1.1", type text}, {"String - Copy.1.2", Int64.Type}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "String - Copy.2", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"String - Copy.2.1", "String - Copy.2.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"String - Copy.2.1", type text}, {"String - Copy.2.2", Int64.Type}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "String - Copy.3", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"String - Copy.3.1", "String - Copy.3.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"String - Copy.3.1", type text}, {"String - Copy.3.2", Int64.Type}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type4", "String - Copy.4", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"String - Copy.4.1", "String - Copy.4.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"String - Copy.4.1", type text}, {"String - Copy.4.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type5",{"String - Copy.1.1", "String - Copy.2.1", "String - Copy.3.1", "String - Copy.4.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"String - Copy.1.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"String - Copy.2.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"String - Copy.1.2", "Period 1"}, {"String - Copy.2.2", "Period 2"}, {"String - Copy.3.2", "Period 3"}, {"String - Copy.4.2", "Period 4"}})
in
    #"Renamed Columns"

method1.PNG

Método 2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fYtLCsAgDETvkmwlqPlYyVHE+1+jiYWuSplZPIZ5a0GDAtpJK2bFVagm/DDssqCHV3OK+As8yAb2hmIulqtY9Bj8aTwvNlRzPazJx5AwED06mZjxMpzD53nN4HjtGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Example = _t, String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Example", Int64.Type}, {"String", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "String", "String - Copy"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"String - Copy", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "String - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"String - Copy", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "String - Copy", Splitter.SplitTextByEachDelimiter({"#"}, QuoteStyle.Csv, true), {"String - Copy.1", "String - Copy.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"String - Copy.1", type text}, {"String - Copy.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"String - Copy.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"String - Copy.2"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Example", "String"}, {{"all", each _, type table [Example=nullable number, String=nullable text, #"String - Copy.2"=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Period", each Table.Column([all],"String - Copy.2")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Period", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Extracted Values", "Period", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Period.1", "Period.2", "Period.3", "Period.4"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Period.1", Int64.Type}, {"Period.2", Int64.Type}, {"Period.3", Int64.Type}, {"Period.4", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type3",{"all"})
in
    #"Removed Columns1"

method2.PNG

Por supuesto, también puede usar DAX:

Period 1 = 
VAR SplitbySemicolon_1 =
    LEFT ( [String], SEARCH ( ";", [String], 1, 0 ) - 1 )
VAR Period_ =
    TRIM (
        RIGHT (
            SUBSTITUTE ( SplitbySemicolon_1, "#", REPT ( " ", LEN ( SplitbySemicolon_1 ) ) ),
            LEN ( SplitbySemicolon_1 )
        )
    )
RETURN
    IF ( Period_ = "", 0, CONVERT ( Period_, DOUBLE ) )
Period 2 = 
VAR SplitbySemicolon_1 =
    LEFT ( [String], SEARCH ( ";", [String], 1, 0 ) - 1 )
VAR Rest_1 =
    RIGHT ( [String], LEN ( [String] ) - LEN ( SplitbySemicolon_1 ) - 1 )
VAR SplitbySemicolon_2 =
    LEFT ( Rest_1, SEARCH ( ";", Rest_1, 1, 0 ) - 1 )
VAR Period_ =
    TRIM (
        RIGHT (
            SUBSTITUTE ( SplitbySemicolon_2, "#", REPT ( " ", LEN ( SplitbySemicolon_2 ) ) ),
            LEN ( SplitbySemicolon_2 )
        )
    )
RETURN
    IF ( Period_ = "", 0, CONVERT ( Period_, DOUBLE ) )
Period 3 = 
VAR SplitbySemicolon_1 =
    LEFT ( [String], SEARCH ( ";", [String], 1, 0 ) - 1 )
VAR Rest_1 =
    RIGHT ( [String], LEN ( [String] ) - LEN ( SplitbySemicolon_1 ) - 1 )
VAR SplitbySemicolon_2 =
    LEFT ( Rest_1, SEARCH ( ";", Rest_1, 1, 0 ) - 1 )
VAR Rest_2 =
    RIGHT ( Rest_1, LEN ( Rest_1 ) - LEN ( SplitbySemicolon_2 ) - 1 )
VAR SplitbySemicolon_3 =
    LEFT ( Rest_2, SEARCH ( ";", Rest_2, 1, 0 ) - 1 )
VAR Period_ =
    TRIM (
        RIGHT (
            SUBSTITUTE ( SplitbySemicolon_3, "#", REPT ( " ", LEN ( SplitbySemicolon_3 ) ) ),
            LEN ( SplitbySemicolon_3 )
        )
    )
RETURN
    IF ( Period_ = "", 0, CONVERT ( Period_, DOUBLE ) )
Period 4 = 
VAR Period_ =
    TRIM (
        RIGHT (
            SUBSTITUTE ( [String], "#", REPT ( " ", LEN ( [String] ) ) ),
            LEN ( [String] )
        )
    )
RETURN
    IF ( Period_ = "", 0, CONVERT ( Period_, DOUBLE ) )

method3.PNG

Para obtener más detalles, consulte el archivo .pbix adjunto.

Saludos

Icey

Si este post ayuda,entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

Syndicate_Admin
Administrator
Administrator

Esta es una solución que usa la interfaz de Power Query.

Haga clic con el botón derecho en la columna de cadena y en dividir por delimitador (use ;

Seleccione la 1ª columna (el resultado de la división)-> Vaya al menú 'Agregar columna' >en la sección Texto,

elija Extract->Text After Delimiter ->use # , y 'From the End of the Input' y 0 (en la sección avanzada).

Eso creará una nueva columna con solo el texto después del último #.

Puede hacerlo para cada columna y, a continuación, quitar las columnas innecesarias, ordenar los datos (reemplace blanks/null por 0) y establecer los tipos de datos correctos.

Déjame cómo te pones

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