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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
o59393
Post Prodigy
Post Prodigy

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 

dfdsfds.JPG

to:

 

sajsakdjasdkajs.JPG

 

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:

 

NumberText

18.9

Ltr

2.5Ltr
1.5Ltr
3Ltr
1.5Ltr
1.25Ltr

 

How can I get it right?

 

Pbix

 

https://1drv.ms/u/s!ApgeWwGTKtFdhnRraJ1oc7BvSq9D?e=sgbaLn

 

Thanks

 

 

 

2 ACCEPTED SOLUTIONS
aj1973
Community Champion
Community Champion

Hi,

Right click and use Add column from example

Add column.PNG

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

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"

View solution in original post

9 REPLIES 9
jdbuchanan71
Super User
Super User

@o59393 

If you right click on your column you can split it at the point where it changes from digit to non-digit.

jdbuchanan71_0-1593461033134.png

We are not able to see your query in the query editor unless you also share the .xlsx file.

jdbuchanan71_0-1593461181909.png

 

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!

aj1973
Community Champion
Community Champion

Hi,

Right click and use Add column from example

Add column.PNG

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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?

aj1973
Community Champion
Community Champion

@o59393 

In power editor :

Select the column

right click

Click on Add column from examples

then write the number in the row here in the picture and click ok

Add column.PNG

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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 . 

jdbuchanan71_0-1593461741468.png

 

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?

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"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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