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

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?

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