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 ...
Need Help
Show and Tell
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,