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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
batmanadk
Helper I
Helper I

Split column by data type

Hello PBI Community! I´m struggling to find the right way to split a column that has either numbers or text in each row. My objective is to have a column made entirely from numbers and the other one from text. I´ve tried the function ´Split Column By¨ in ¨Transform¨, by applying ¨By non-Digit to Digit¨ but it has only worked in columns where the digits where really small (1 digit only) or where cells had both text and number in it. My intention is to leave mixed rows (that have both text and numbers) in the text column. I´ll show what happenend in screenshots down below. 

 
 

I´ve thought about some kind of conditional column where if the row in one column has number then the new column would only have rows equal to that, but I don´t know if there´s that functionality.... I really appreciate the help and will be looking forward to hearing from you guys, since I have to present something on monday... Thanks a lot!

Ssh1.png

 

Ssh 2.png

 

 

1 ACCEPTED SOLUTION

The simple way of doing this is to:

1. Duplicate the column.

2. Then change the duplicated column to the number type

3. Replace errors with nulls

4. Then add a conditional column with if the duplicated column is null then the origional column, else null.

 

Using a custom column mentioned above with try would be:

= let parsed = try Number.From([Origional Text]) in [Number = parsed[Value]?, Text = if parsed[HasError] then [Origional Text] else null]

Then just expand the record column. If you want to avoid having to set the type of the column, then alter the Added column step to add a 3rd parameter: type [Number = number, Text = text]

View solution in original post

8 REPLIES 8
v-juanli-msft
Community Support
Community Support

Hi @batmanadk 

If any of the answers slove your problem,  could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.
It is appreciated to share some typical sample data.
 
Best Regards
Maggie
mahoneypat
Microsoft Employee
Microsoft Employee

You could make two custom columns (one for numbers and one for text) with the Text.Select() function, as follows:

 

Number column

= Text.Select([TextColumn], {"0".."9"})

 

Text Column

= Text.Select([TextColumn], {"A".."Z", "a".."z"})

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hey Pat, thanks a lot! It almost worked, the problem is that there are some mixed cells, and when that happens, I don't need the numbers getting pulled to the number column. By adding "0".."9"} to the new text column I was able to get the full column, but in the other one, it's pulling only the number (Where it shouldn't be pulling anything at all)

So this hapens

 

Original Text ColumnNew Number ColumnNew Text Column
Barra 11Barra1

 

Thanks a lot for the help!

The simple way of doing this is to:

1. Duplicate the column.

2. Then change the duplicated column to the number type

3. Replace errors with nulls

4. Then add a conditional column with if the duplicated column is null then the origional column, else null.

 

Using a custom column mentioned above with try would be:

= let parsed = try Number.From([Origional Text]) in [Number = parsed[Value]?, Text = if parsed[HasError] then [Origional Text] else null]

Then just expand the record column. If you want to avoid having to set the type of the column, then alter the Added column step to add a 3rd parameter: type [Number = number, Text = text]

What you also want to do is take advantage of the Power Query TRY ... OTHERWISE construct.

 

TRY to read the column as number OTHERWISE read it as text.

 

 

Anonymous
Not applicable

waiting for a better or more elegant solution, try this:

 

Table.AddColumn(Table.AddColumn(yourtab, "num", each if Value.Is([yourMixedCol], type number) then [yourMixedCol] else null), "txt", each if Value.Is([yourMixedCol], type text) then [yourMixedCol] else null)

 

Hello! Sorry for my ignorance, I´m kind of a noob here. what would I need to write where you wrote your tab? I´m in PowerQuery, so I´ve tried writing the table name, even though I knew that wasn´t it.... I have it like this :

Table.AddColumn(Table.AddColumn(yourtab, "num", each if Value.Is([Name], type number) then [Name] else null), "txt", each if Value.Is([Name], type text) then [Name] else null)

Thanks for your help!!

Anonymous
Not applicable

hi @batmanadk,

 

yourtab  in my statemnent is just the result variable of previous step: usully somethink like #"expanded ...".

Then you have only to find this identifier and put in place of yourtab.

 

good luck

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors