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
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!
Solved! Go to 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]
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?
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
To learn more about Power BI, follow me on Twitter or subscribe 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 Column | New Number Column | New Text Column |
Barra 1 | 1 | Barra1 |
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.
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!!
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
61 | |
19 | |
17 | |
13 |