Forum Discussion
Solved
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 t...
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"
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!
Ahh, the . is a switch to non-digit, gross. What about splitting by delimiter (space)?
Hmm weird, the table cannnot be displayed from the report tab for example? or the last step of the power query?
It's can't, no but you can also modify the split line to include the "." in the part to keep.
= Table.SplitColumn(#"Changed Type", "Column", Splitter.SplitTextByCharacterTransition({"0".."9","."}, (c) => not List.Contains({"0".."9","."}, c)), {"Column.1", "Column.2"})So it splits it when it goes form 0-9 OR . to something that is not 0-9 OR .
Im doing this on the M code but getting an error:
let Source = Excel.Workbook(File.Contents("C:\Users\O59393\Documents\Vol Capacity.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], Table.SplitColumn(#"Changed Type", "Column", Splitter.SplitTextByCharacterTransition({"0".."9","."}, (c) => not List.Contains({"0".."9","."}, c)), {"Column.1", "Column.2"}) in #"Filtered Rows"What should I fix?