Splitting Columns without generating new column

[Till__]

Dear Community, 

I want to split serveral columns after if it changes from number to text. For example the entry is 2xyx and I only want to have the 2 left over.

Test

2xyx

Normaly a new column is created also with the xyx text. 

Test	Test.1
2	xyx

How do I split the Column without creating the new column? 

Test

2

Thank you in advance! 

[ronrsnfld]

• Split the column as you would normally
• Select that applied step and examine the Formula Bar:

Note that there is a "List" at the end of that function that names the two columns.

• In the formula bar, delete the second column name, and, optionally, change the first columns name

Voila!

If you need to do this 40 times, I would suggest NOT doing it from the User Interface, but rather going into the 

Advanced Editor and using the List.Accumulate function to repeat this for all the relevant columns. 

For Example:

Given:

and wanting to return just the numeric portion of all the columns, you can use this code in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMqqorFLSUTIxNUtMSgYyDCtAXCMDk4qKCqVYnWglYyMTiIy5hWUFkDJLqgSSxgaGINHYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t, T2 = _t, T3 = _t, T4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}, {"T2", type text}, {"T3", type text}, {"T4", type text}}),

//create list of columns to be split.
//Many ways to do this, but for all the columns:
    #"Columns to Split" = Table.ColumnNames(#"Changed Type"),

//Split all the columns:
    #"Splitted Columns" = List.Accumulate(
        #"Columns to Split",
        #"Changed Type",
        (s,c)=> Table.SplitColumn(s,c,Splitter.SplitTextByCharacterTransition(
                    {"0".."9"},(c)=>not List.Contains({"0".."9"},c)),{c}))
in
    #"Splitted Columns"

to get:

[AntrikshSharma]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMqqorFCK1QEzjCsqE5NMLUws9QyNTItzrBPTlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),

    
    Extract = 
        Table.TransformColumns (
            Source,
            {
                "Test",
                ( ColumnValue ) =>
                    let
                        Split = 
                            Text.ToList ( ColumnValue ),
                        SelectNumbers = 
                            List.Select (
                                Split,
                                ( x ) => List.Contains ( { "0" .. "9" }, x )
                            ),
                        Combine = 
                            Text.Combine ( SelectNumbers ),
                        ToNumber = 
                            Number.From ( Combine )
                    in
                        ToNumber
            }
        ),
    ChangeType = 
        Table.TransformColumnTypes ( 
            Extract, 
            { "Test", Int64.Type } 
        )
in
    ChangeType

[PwerQueryKees]

Back at my laptop.

Started with this test data:

This query:

let
    Source = YourDataSource,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Any Column 1", "Any Column 2"}, "Attribute", "Value"),  // Here you mention the OTHER columns. Generetae it in the UI by selecting the other columns and select "UnPivot Other Columns"
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), {"Attribute"}), // The split. I used the UI to generate the split and then removed the second column manually
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

The unpivot was created in the UI and changed manually.

The big advantage of the Table.UnpivotOtherColumns is that additional columns will be taken care of automatically.

The Table.Pivot was created in UI like this:

Results in this:

Same data, different column names.

2 changes: Rows and columns are both sorted....

[PwerQueryKees]

I just realized I misread. I was splitting the column names, but you need the values to be split.

Works almost the same:

let
    Source = YourDataSource,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Any Column 1", "Any Column 2"}, "Attribute", "Value"),  // Here you mention the OTHER columns. Generetae it in the UI by selecting the other columns and select "UnPivot Other Columns"
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), {"Value"}), // The split. I used the UI to generate the split and then removed the second column manually
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

to go from this:

to this

[GeeMo]

Pretty old question, so I realize no one may ever see this, but...

Edit the code in the Formula Bar to remove the .1 from the first resulting column (so the result will be named the same as it is now), then delete any other columns after that from the formula.

So instead of this, the default behavior:

= Table.SplitColumn(#"Added Custom2", "Test", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Test.1", "Test.2"})

Edit the end of the formula to this:

= Table.SplitColumn(#"Added Custom2", "Test", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Test"})

It has the effect of just dropping everything after the 2 in the same column without creating any new ones

[KeyurPatel14]

Hii @Till__ ,
I can help you to make this logic dynamic if you provide more data.
 If this logic/idea helps you then consider this as a solution and give a kudos.

[KeyurPatel14]

Hii @Till__ ,
I tried with the data you provided and it is working perfect.
But I think it will not work if you enter more data but the concept/logic will remain same.
Here is the M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMqqorFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}}),
#"Extracted First Characters" = Table.TransformColumns(#"Changed Type", {{"Test", each Text.Start(_, 1), type text}})
in
#"Extracted First Characters"


I can help you further if you post more accurate data. 

[Till__]

I was trying to do something like that but this didnt work... 

= Table.TransformColumns(#"Umbenannte Spalten1", {{List.Select(Table.ColumnNames(#"Umbenannte Spalten1"), each Text.Contains(_, "EF-")), each Text.Start(_, 1), type text}})

[Till__]

Thank you for your post and your help @KeyurPatel14 !

The columns have all the prefix of "EF-" and then an individul numeric ID. At the moment I do it quite manually like that. The text in the cells in the columns is always like that: "3) Some random Text"  3 can be any number and the text may have differ in the length. 

#"Split1"= Table.SplitColumn(#"Umbenannte Spalten1", "EF-1.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.1.1)"}),
#"Split2"= Table.SplitColumn(#"Split1", "EF-1.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.1.2)"}),
#"Split3"= Table.SplitColumn(#"Split2", "EF-1.2.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.2.1)"}),
#"Split4"= Table.SplitColumn(#"Split3", "EF-1.2.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.2.1)"}),
#"Split5"= Table.SplitColumn(#"Split4", "EF-1.2.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.2.2)"}),
#"Split6"= Table.SplitColumn(#"Split5", "EF-1.3.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.3.1)"}),
#"Split7"= Table.SplitColumn(#"Split6", "EF-1.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-1.3.2)"}),
#"Split8"= Table.SplitColumn(#"Split7", "EF-2.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-2.1.1)"}),
#"Split9"= Table.SplitColumn(#"Split8", "EF-2.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-2.1.2)"}),
#"Split10"= Table.SplitColumn(#"Split9", "EF-2.2.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-2.2.1)"}),
#"Split11"= Table.SplitColumn(#"Split10", "EF-2.3.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-2.3.1)"}),
#"Split12"= Table.SplitColumn(#"Split11", "EF-2.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-2.3.2)"}),
#"Split13"= Table.SplitColumn(#"Split12", "EF-3.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-3.1.1)"}),
#"Split14"= Table.SplitColumn(#"Split13", "EF-3.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-3.1.2)"}),
#"Split15"= Table.SplitColumn(#"Split14", "EF-3.1.3)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-3.1.3)"}),
#"Split16"= Table.SplitColumn(#"Split15", "EF-3.2.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-3.2.2)"}),
#"Split16.1"= Table.SplitColumn(#"Split16", "EF-3.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-3.3.2)"}),
#"Split17"= Table.SplitColumn(#"Split16.1", "EF-4.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-4.1.1)"}),
#"Split18"= Table.SplitColumn(#"Split17", "EF-4.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-4.1.2)"}),
#"Split19"= Table.SplitColumn(#"Split18", "EF-4.2.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-4.2.2)"}),
#"Split20"= Table.SplitColumn(#"Split19", "EF-4.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-4.3.2)"}),
#"Split21"= Table.SplitColumn(#"Split20", "EF-5.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.1.1)"}),
#"Split22"= Table.SplitColumn(#"Split21", "EF-5.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.1.2)"}),
#"Split23"= Table.SplitColumn(#"Split22", "EF-5.2.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.2.1)"}),
#"Split24"= Table.SplitColumn(#"Split23", "EF-5.2.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.2.2)"}),
#"Split25"= Table.SplitColumn(#"Split24", "EF-5.3.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.3.1)"}),
#"Split26"= Table.SplitColumn(#"Split25", "EF-5.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-5.3.2)"}),
#"Split27"= Table.SplitColumn(#"Split26", "EF-6.1.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-6.1.1)"}),
#"Split28"= Table.SplitColumn(#"Split27", "EF-6.1.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-6.1.2)"}),
#"Split29"= Table.SplitColumn(#"Split28", "EF-6.2.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-6.2.2)"}),
#"Split30"= Table.SplitColumn(#"Split29", "EF-6.3.1)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-6.3.1)"}),
#"Split31"= Table.SplitColumn(#"Split30", "EF-6.3.2)", Splitter.SplitTextByDelimiter(")", QuoteStyle.None), {"EF-6.3.2)"}),

[PwerQueryKees]

this is screaming UnPivot to me....

So unpivot the columns

Then you get an atttribute coumn with the columnname value column with the value

The you only have to do the split once

I suspect the rest of you queries will be a lot easier too! If you really want the fields back, you can do a pivot. Not at my laptop, but you pivot the attribute colum and take the value column as value parameter and select no aggregation or keep all rows (i am not sure) for the pivot parameters.

[jgordon11]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(
        "LVRHtuAgDLvLX8+CEtpZ5s0iFQLppACnn9j5Oz8SZElY/vv3h1P11HP/8+eHcRH3Ob8V
        VcSnxkFFud3uBipCSvDXW1WELauNb8WJsrG0P//+/P3R2ji35e9P57v9rUwlY2P9W2nNb
        fE1fDViqpsOzqi08ZngrDLHbS/EqQSp1+uA75z1a4SOsrrOERpWit/XCdBUa7tN41spXa
        U0QZNKyngP7qNjyBFmCyQ1b0IBYkyqbkUxhunSjy0KpJvPIF9qumwRuhghlyd8sgzjITe
        Aw5jd4wqfCR2X7oSG1JyHR31Mb+swYRO2788MFSWrbXqEYZo9pcApVyJ2V/9JSG3AO0J3
        WxreShDVPi0gcsFPGwqcSeeaGmEEFX0bazQnpRrkG67sPSNtpbp4BuBVhfHY4KrgU/bzZ
        6bzd0QUTs3RW6AgGEluh0oJlutcozw2DD3QUkrdSywIaIq34JygNNmSPjaMjV3/gBQm8r
        kl+NOMU3OhTt1ui0cSurUPOMc0fcXBBfVOXTuun8Wc9vPR4DzpOJ8t3qH5HKC1ktXkFtR
        HRS4OECVX5z3e6BzpN3cgjuRsnxJMDjN6XkOHONXoWzBA8Kr2ccJZlHNXyleV9CQcEmIv
        Hz4+UvRdAb5CvX1SwJFgLnuHLrP1wBwoQge/B6zItkTQxQ1ru/zZTMn0zNimMmGYEJDop
        cWR1xUNZQGprFLl8ugy4925NegiTaf7DYShgzthOIymZ67hDpd02PsaKVJnrxtv+3cs0U
        Xj5g2nSZrNNs8vDFlCsJg2Yo+x/yZ52TYcjzd1Y9kRkIeE6ZZMFZsD7gN2zHn6Hp0IV9+
        AYwzb+7Jh4mUcbvTpHbMmrZgNdU7pQueVi/eCbylqX3/uVJLv/jc75pwH//VOO4aaUnJ1
        MX/Jap8DnRf6mWuoFCOHe6f/2zvV+hwF88aX/ODLGGH7+0CObZ42jJNocpO+VZSPcn6bw
        y9N+JVl0mUxjITO090hjFrvES3Tqj3nHWVxv9VITNBxqEGW0KSd3WezVCbMuKsYleuSLK
        ZMRdvgwkhxghuU63q7cKPpuT0TBov0Mb/L699/", 
        BinaryEncoding.Base64), Compression.Deflate))),
    // list of column names to operate on
    tcn = Table.ColumnNames(Source),
    c = Character.FromNumber,
    // non-numeric character list
    nn = List.Buffer(List.RemoveItems({c(0)..c(255)}, {"0".."9"})),
    TransformOperations = List.Accumulate(tcn, {}, (s,c)=> s & 
        {{c, each Number.From(Text.Start(_, Text.PositionOfAny(_, nn)))}}),
    Result = Table.TransformColumns(Source, TransformOperations)
in
    Result

[JadhavVarsha_13]

hello @Till__ 

You do this using Split Columns option from "Transform" ribbon 

Split columns by delimiter - Power Query | Microsoft Learn

[Till__]

Sorry, perhabs my question was not so clear or I am lacking of understanding your response, but I have to do this 40 times manually so I want to avoid the pain to split each column and delte the column with the text which I do not need... Do you have an option to do this? I do not know what you meant by Transform ribbon.

[saud968]

Try this

Open Power Query Editor:
In Power BI Desktop, go to the “Home” tab and click on “Transform data” to open the Power Query Editor.
Create a Custom Function:
Go to the “Home” tab and click on “Advanced Editor”.
Create a new custom function to extract the numeric part from the text. Here’s an example of how to define this function:
let
ExtractNumeric = (inputText as text) as text =>
let
// Remove the prefix "EF-"
textWithoutPrefix = Text.Replace(inputText, "EF-", ""),
// Extract the numeric part before the closing parenthesis
numericText = Text.BeforeDelimiter(textWithoutPrefix, ")")
in
Text.Trim(numericText)
in
ExtractNumeric

Apply the Custom Function to Multiple Columns:
Go back to the main query where your data is.
For each column you want to transform, add a custom column that uses the ExtractNumeric function.
Here’s how you can do it:
Go to the “Add Column” tab and click on “Custom Column”.
Name the new column (e.g., NumericPart_Column1).
Use the custom function in the formula box:
ExtractNumeric([Column1])

Repeat this step for each column you want to transform (e.g., Column2, Column3, etc.).
Remove Original Columns:
After creating the new columns with the numeric parts, you can remove the original columns if you no longer need them.
Right-click on each original column and select “Remove”.
Close and Apply:
Click on “Close & Apply” to apply the changes and return to the Power BI report view.
Example
If your columns have values like EF-3) Some random Text, the new columns will have just the numeric part:

Before:

Column1
EF-3) Some random Text

After:

NumericPart_Column1
3