Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hola, estoy tratando de extraer un código específico de una cadena de texto.
En la "Columna de datos de ejemplo" el formato del código siempre es "00-0000" sin embargo no he tenido suerte con las funciones de extracción y división, ya que la cadena varía demasiado y a veces hay más de un código.
Vea el ejemplo a continuación:
Además, si hay varios códigos en una cadena, me gustaría que se sentaran en una columna con un punto y coma y, a continuación, agregar un prefijo basado en otra columna. El resultado deseado está por encima.
También he adjuntado un PBIX en Dropbox que contiene los datos de ejemplo.
Cualquier puntero sería apreciado
Solved! Go to Solution.
Ahí, @FruitandVeg
Según su descripción, creo que primero se puede extraer el número y el carácter "-", y luego recortarlo. El lenguaje M específico es el siguiente:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY+xDoQwDEN/xepMkNNyn3EbW8VYiaVCAob7/AutRGFAYoueYzuJ0W1LTtjTb4f6MEApVBIF52Wtmpu66A7Jk3cFgpYwpzWV1YbaFCjm5sUaegWtVYivwX40KGfGuzr4EqsXrLXo8RBz6OMhZg4fX0Wpr09/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sample Column Data" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Select([Sample Column Data],{" ".."9"})),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Sample Column Data"}, "Attribute", "Value"),
#"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Trim, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Trimmed Text"," "," ",Replacer.ReplaceText,{"Value"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," "," ",Replacer.ReplaceText,{"Value"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5"}),
#"Added Conditional Column" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if Text.Contains([Value.1], "0-") then [Value.1] else null),
#"Replaced Value2" = Table.ReplaceValue(#"Added Conditional Column",null,"0",Replacer.ReplaceValue,{"Value.3", "Value.4"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value2",{"Value.5"}),
#"Replaced Value3" = Table.ReplaceValue(#"Removed Columns",null,"",Replacer.ReplaceValue,{"Value.2"}),
#"Trimmed Text1" = Table.TransformColumns(#"Replaced Value3",{{"Value.2", Text.Trim, type text}}),
#"Added Conditional Column1" = Table.AddColumn(#"Trimmed Text1", "Custom.1", each if Text.Contains([Value.2], "0-") then [Value.2] else null),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each if Text.Contains([Value.3], "0-") then [Value.3] else null),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Custom.3", each if Text.Contains([Value.4], "0-") then [Value.4] else null),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column3",{"Attribute", "Value.1", "Value.2", "Value.3", "Value.4"}),
#"Merged Columns" = Table.CombineColumns(#"Removed Columns1",{"Custom", "Custom.1", "Custom.2", "Custom.3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Added Prefix" = Table.TransformColumns(#"Merged Columns", {{"Merged", each ";" & _, type text}}),
#"Replaced Value4" = Table.ReplaceValue(#"Added Prefix",";;",";",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4",";",";ABC",Replacer.ReplaceText,{"Merged"}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Replaced Value5", {{"Merged", each Text.AfterDelimiter(_, ";"), type text}})
in
#"Extracted Text After Delimiter"
La salida final se muestra a continuación:
Saludos
Apoyo comunitario Team_ Yalan Wu
Si este post ayuda, entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
Ahí, @FruitandVeg
Según su descripción, creo que primero se puede extraer el número y el carácter "-", y luego recortarlo. El lenguaje M específico es el siguiente:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY+xDoQwDEN/xepMkNNyn3EbW8VYiaVCAob7/AutRGFAYoueYzuJ0W1LTtjTb4f6MEApVBIF52Wtmpu66A7Jk3cFgpYwpzWV1YbaFCjm5sUaegWtVYivwX40KGfGuzr4EqsXrLXo8RBz6OMhZg4fX0Wpr09/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sample Column Data" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Select([Sample Column Data],{" ".."9"})),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Sample Column Data"}, "Attribute", "Value"),
#"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Trim, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Trimmed Text"," "," ",Replacer.ReplaceText,{"Value"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," "," ",Replacer.ReplaceText,{"Value"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5"}),
#"Added Conditional Column" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if Text.Contains([Value.1], "0-") then [Value.1] else null),
#"Replaced Value2" = Table.ReplaceValue(#"Added Conditional Column",null,"0",Replacer.ReplaceValue,{"Value.3", "Value.4"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value2",{"Value.5"}),
#"Replaced Value3" = Table.ReplaceValue(#"Removed Columns",null,"",Replacer.ReplaceValue,{"Value.2"}),
#"Trimmed Text1" = Table.TransformColumns(#"Replaced Value3",{{"Value.2", Text.Trim, type text}}),
#"Added Conditional Column1" = Table.AddColumn(#"Trimmed Text1", "Custom.1", each if Text.Contains([Value.2], "0-") then [Value.2] else null),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each if Text.Contains([Value.3], "0-") then [Value.3] else null),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Custom.3", each if Text.Contains([Value.4], "0-") then [Value.4] else null),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column3",{"Attribute", "Value.1", "Value.2", "Value.3", "Value.4"}),
#"Merged Columns" = Table.CombineColumns(#"Removed Columns1",{"Custom", "Custom.1", "Custom.2", "Custom.3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Added Prefix" = Table.TransformColumns(#"Merged Columns", {{"Merged", each ";" & _, type text}}),
#"Replaced Value4" = Table.ReplaceValue(#"Added Prefix",";;",";",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4",";",";ABC",Replacer.ReplaceText,{"Merged"}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Replaced Value5", {{"Merged", each Text.AfterDelimiter(_, ";"), type text}})
in
#"Extracted Text After Delimiter"
La salida final se muestra a continuación:
Saludos
Apoyo comunitario Team_ Yalan Wu
Si este post ayuda, entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
1 | |
1 | |
1 | |
1 | |
1 |