Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Power Query - Spilt Column From Digit to Non-digit Once

Hello,

 

I need help with Power Query for something. I have a column that has both addresses and 6-7 digits student ID at the end of each row for that column, and I need to split them out into two column, one contain only addresses and one only student ID.

I figured using Split column from digit to non-digit would be perfect for this case but I don't know how to adjust the query so that it would split the column right to left instead, and I only need it to do the split once since the addresses also have number in it.

 

Example:

Address & ID
ABC 3 DEF Road 123456

=>

AddressID
ABC 3 DEF Road

123456

 

Does anybody have any idea how I can make this happen or is this even possible within Power Query?

Thanks in advance, help is much appreciated!!

Need Help
Show and Tell

5 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    2 years ago

    Anonymous ,

    If the ID is always 6 digits use:

    =text.end( [Address & ID], 6).

    Then you can Trim the address column to get rid of the last " ".

    Happy Holidays.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks for the help, but unfortunately some of them is 6 digits and some is 7 digits so I need to do split from digit to non-digit instead 😞

      Happy holidays!!

      • rsbin's avatar
        rsbin
        Community Champion
        2 years ago

        Anonymous ,

        There are a myriad of text functions available to you.
        https://learn.microsoft.com/en-us/powerquery-m/text-functions
        You just need to find a common characteristic that helps identify where the ID starts. 

        For example:  Do they always start with a "1" or "12"?  Does the Address always end with "Road"?
        You can substitute the " " (space) between the end of the Address and the start of the ID with another character ( "~"), then use that as your Delimiter.
        You just have to get creative to find the right combination that will work for your various scenarios.
        Regards,

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    How about splitting by the first space from the right side? You can do that with just the GUI.

     

    You could also do it as two custom columns.

    Address = Text.TrimEnd([#"Address & ID"], {"0".."9", " "})
    ID = Text.AfterDelimiter([#"Address & ID"], [Address])

     

    Full sample query:

    let
    Source = Table.FromRows({{"ABC3 DEF Road 123456"}}, {"Address & ID"}),
    #"Added Address" = Table.AddColumn(Source, "Address", each Text.TrimEnd([#"Address & ID"], {"0".."9", " "})),
    #"Added ID" = Table.AddColumn(#"Added Address", "ID", each Text.AfterDelimiter([#"Address & ID"], [Address]))
in
    #"Added ID"