Forum Discussion

Raveen's avatar
Raveen
Frequent Visitor
4 years ago
Solved

Extract multiple number values from text

Hi All,

 

Need to extract number values from text, the number should be 3 digits long.
every extraction should be on new column.

Textkolom1kolom2kolom3
111 A 222 ab 333 text111222333
11 A 444 some text444  
text 111 c 22 ab 333111333 
sometext11122 A 333333  
44 A 222 d 3333222  
text12 A 22223   
222 ab 333222333 

 

help is greatly appreciated.

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VHBUMDIyUkhMUjA2NlYoSa0oMTO3UIrVAUkC5UxMTBSK83NTwTJgYRBDAaQxWQGuDywBUgaSBMoBJRzh4kATIHakgISM4YYYGkHEjSBCRkimxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Trim(Text.Select([Text],{"0".."9"," "}))),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each (Text.Length([Custom]) = 3)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Text"}, {{"kolom", each Text.Combine([Custom],","), type nullable text}}),
    Custom1 = Table.SplitColumn(#"Grouped Rows", "kolom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(#"Grouped Rows", "Temp", each List.Count(Text.Split([kolom],",")))[Temp])},each "kolom." & Number.ToText(_)))
in
    Custom1

     

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VHBUMDIyUkhMUjA2NlYoSa0oMTO3UIrVAUkC5UxMTBSK83NTwTJgYRBDAaQxWQGuDywBUgaSBMoBJRzh4kATIHakgISM4YYYGkHEjSBCRkimxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Trim(Text.Select([Text],{"0".."9"," "}))),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each (Text.Length([Custom]) = 3)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Text"}, {{"kolom", each Text.Combine([Custom],","), type nullable text}}),
    Custom1 = Table.SplitColumn(#"Grouped Rows", "kolom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(#"Grouped Rows", "Temp", each List.Count(Text.Split([kolom],",")))[Temp])},each "kolom." & Number.ToText(_)))
in
    Custom1

     

    • Raveen's avatar
      Raveen
      Frequent Visitor
      4 years ago

      Vijay_A_Verma Great this is what i needed.

      Now trying to implement in my existing query, when i try to keep a extra column(Row id) when grouping the result is new column with list values.

      Is there a better way to do this?

       

      Also it would be nice to keep the orginal column with the summarized values, maybe even new kolom with the account.

       

      Anyhow accepted it as a solution since my orginal question has been solved.

      Thanks Again

       



      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        Use this

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc07EoAgDATQq2RS2yQg2HIOhsIPpWMhhcc3CQ5jyb7NkjMSESRgZlg3cM5Bq08LccEJCcukBXHvPdzXWU2F2EgfoAM7jHtBZ6h1LYgLps+8maz1Pw+NNZ/HIHE31jhYzP/1iKW8", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t, #"Row ID" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Trim(Text.Select([Text],{"0".."9"," "}))),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each (Text.Length([Custom]) = 3)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Text", "Row ID"}, {{"kolom", each Text.Combine([Custom],","), type nullable text}})
in
    #"Grouped Rows"