Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Till__
Helper I
Helper I

Splitting Columns without generating new column

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. 

TestTest.1
2xyx


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

Test

2

 

Thank you in advance! 

7 REPLIES 7
KeyurPatel14
Resolver IV
Resolver IV

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
Resolver IV
Resolver IV

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. 

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}})

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)"}),

jgordon11
Resolver II
Resolver II

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
Resolver II
Resolver II

hello @Till__ 

 

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

 

Split columns by delimiter - Power Query | Microsoft Learn

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors