cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Split column based on text and numerical characters

Hi,

 

I'm trying to split a column that contains text and number components into two seperate columns, one containing the text characters and another containing the numbers. 

 

i.e. I want to go from the single column:

 

AB143

A122

VHG309

X12

XX13

 

to two columns containing:

 

AB                   143

A                     122

VHG                309

X                     12

XX                   13

 

Is this possible within Power BI?

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Split column based on text and numerical characters

Instead I would propose Text.PositionOfAny as illustrated in this video.

 

Relevant parts of the code generated during video recording ("PreviousStep" is the name of your previous step):

 

    #"Added Custom" = Table.AddColumn(PreviousStep, "Custom", each Text.PositionOfAny([Column1],{"0".."9"})),
    #"Inserted First Characters" = Table.AddColumn(#"Added Custom", "First Characters", each Text.Start([Column1], [Custom]), type text),
    #"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Text Range", each Text.Middle([Column1], [Custom]), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Range",{"Column1", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Range", "Number"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Number", Int64.Type}})
Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
Highlighted
Super User IX
Super User IX

Re: Split column based on text and numerical characters

You could potentially use PostionOfAny function:

 

List.PositionOfAny("ABCBA", {"A","B"}, Occurrence.All) equals {0,1,3,4}  

You would probably have to use an M custom function or another function to return the lowest value in the list and then feed that to a Split.

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Champion
Community Champion

Re: Split column based on text and numerical characters

Instead I would propose Text.PositionOfAny as illustrated in this video.

 

Relevant parts of the code generated during video recording ("PreviousStep" is the name of your previous step):

 

    #"Added Custom" = Table.AddColumn(PreviousStep, "Custom", each Text.PositionOfAny([Column1],{"0".."9"})),
    #"Inserted First Characters" = Table.AddColumn(#"Added Custom", "First Characters", each Text.Start([Column1], [Custom]), type text),
    #"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Text Range", each Text.Middle([Column1], [Custom]), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Range",{"Column1", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Range", "Number"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Number", Int64.Type}})
Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Frequent Visitor

Re: Split column based on text and numerical characters

That works well (& video was very helpful).

 

Thanks.

Highlighted
New Member

Re: Split column based on text and numerical characters

If you could get your cell addresses as $A$1 rather that A1, you could simply split columns based on the delimiter $.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Come join us today! Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors