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

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors