Forum Discussion

o59393's avatar
o59393
Post Prodigy
6 years ago
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...
Need Help
Show and Tell
  • aj1973's avatar
    aj1973
    6 years ago

    Hi,

    Right click and use Add column from example

  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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"
o59393's avatar
o59393
Post Prodigy
6 years ago

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's avatar
aj1973
Community Champion
6 years ago

Hi,

Right click and use Add column from example