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"
jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

o59393 

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.

 

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

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    Ahh, the . is a switch to non-digit, gross.  What about splitting by delimiter (space)?

    • o59393's avatar
      o59393
      Post Prodigy
      6 years ago

      Hmm weird, the table cannnot be displayed from the report tab for example? or the last step of the power query?

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        6 years ago

        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 .