Forum Discussion
Extract number and text from string
Hi all
I need to extract the number and text in separated columns from a string. I tried out with power query's split column digit to non digit but didnt get the number as one:
From
to:
As seen on first row, i got the .9 in another column but should be as a one "18.9"
The expected result would be something like this:
| Number | Text |
18.9 | Ltr |
| 2.5 | Ltr |
| 1.5 | Ltr |
| 3 | Ltr |
| 1.5 | Ltr |
| 1.25 | Ltr |
How can I get it right?
Pbix
https://1drv.ms/u/s!ApgeWwGTKtFdhnRraJ1oc7BvSq9D?e=sgbaLn
Thanks
Hi,
Right click and use Add column from example
Not sure, try this (modified from the advanced editor in your .pbix)
let Source = Excel.Workbook(File.Contents("C:\Users\O59393\Documents\Vol Capacity.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Split Column by Character Transition" = Table.SplitColumn(#"Promoted Headers", "[ L2.3 - Vol Capacity ]", Splitter.SplitTextByCharacterTransition({"0".."9","."}, (c) => not List.Contains({"0".."9","."}, c)), {"[ L2.3 - Vol Capacity ].1", "[ L2.3 - Vol Capacity ].2"}) in #"Split Column by Character Transition"
9 Replies
If you right click on your column you can split it at the point where it changes from digit to non-digit.
We are not able to see your query in the query editor unless you also share the .xlsx file.
Hi jdbuchanan71
That's exactly how I did it but as seen in the image I posted the number after the dot was separated.
If you look on my pbix you will see I used your method.
Thanks!Hi,
Right click and use Add column from example
