none
How do I get Text from a string RRS feed

  • Question

  • Hi All

    I'm hoping you power query guru's can help me out with this?

    I have a string from a database that contains a lot of text. here's an example string:

    ----------------------------------------------------------------------------------------------

    User:jane.doe executed a client search from Client View portal. Last Client Search Criteria:ClientSearchCriteria  Search By:SIN  Client Surname:Undefined.  Client FirstName:Undefined.  Client Brn:Undefined.  Client Date Of Birth:Undefined.  Client Health Card Number:Undefined.  Client Institution Ids:  10000001,  10000002,  Client Number:1234567  Client Source Owner Organization Code:NE  . Number of records matching the search criteria:1

    ----------------------------------------------------------------------------------------------

    I want to be able to get the text directly after each ":"

    the first section, User: I want to get the string 'jane.doe'

    second section, Search Criteria: I want to get 'ClientSearchCriteria'

    third section, Search By: I want to get 'SIN'

    and so on.

    Anyone able to help me figure this out?


    • Edited by BillBet Friday, November 27, 2015 1:38 PM
    Thursday, November 26, 2015 9:28 PM

Answers

  • You can spit the text into a list with the Text.Split function and add a few other transformations as in the example below:

    let
        Text = "User:jane.doe executed a client search from Client View portal. Last Client Search Criteria:ClientSearchCriteria  Search By:SIN  Client Surname:Undefined.  Client FirstName:Undefined.  Client Brn:Undefined.  Client Date Of Birth:Undefined.  Client Health Card Number:Undefined.  Client Institution Ids:  10000001,  10000002,  Client Number:1234567  Client Source Owner Organization Code:NE  . Number of records matching the search criteria:1",
        #"Split by :" = Text.Split( Text, ":"),
        #"Skip 1" = List.Skip(#"Split by :",1),
        #"Keep first word" = List.Transform( #"Skip 1" , each Text.Split(_," "){0})
    in
        #"Keep first word"
    Then you can address each list member by its position index: listname{position}


    Friday, November 27, 2015 1:39 PM

All replies

  • You can spit the text into a list with the Text.Split function and add a few other transformations as in the example below:

    let
        Text = "User:jane.doe executed a client search from Client View portal. Last Client Search Criteria:ClientSearchCriteria  Search By:SIN  Client Surname:Undefined.  Client FirstName:Undefined.  Client Brn:Undefined.  Client Date Of Birth:Undefined.  Client Health Card Number:Undefined.  Client Institution Ids:  10000001,  10000002,  Client Number:1234567  Client Source Owner Organization Code:NE  . Number of records matching the search criteria:1",
        #"Split by :" = Text.Split( Text, ":"),
        #"Skip 1" = List.Skip(#"Split by :",1),
        #"Keep first word" = List.Transform( #"Skip 1" , each Text.Split(_," "){0})
    in
        #"Keep first word"
    Then you can address each list member by its position index: listname{position}


    Friday, November 27, 2015 1:39 PM
  • Thank you very much. :-)
    Friday, November 27, 2015 2:29 PM