Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
ejemplo | cuerda | Período 1 | Período 2 | Período 3 | Período 4 |
1 | 52.50#50#54;54.00#54#54;54.00#54#54;54.00#54#54 | 54 | 54 | 54 | 54 |
2 | 0.00#0#0;0.00#0#0;37.67#21#46;46.00#46#46 | 0 | 0 | 46 | 46 |
3 | 0.00#0#0;0.00#0#0;46.00#36#56;56.00#56#56 | 0 | 0 | 56 | 56 |
4 | ##;##;93.33#86#97;96.00#96#96 | 0 | 0 | 97 | 96 |
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?
Solved! Go to Solution.
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
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"
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"
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 ) )
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.
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"
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"
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 ) )
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.
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