Extracting state and country from unstructured address field

[jaharris91]

I have a data source that is pulling in address information in a single column and there isn't any structure to how that information is input (see example)

Speech Address

Raleigh, NC Convention Center

3799 Las Vegas Blvd, Las Vegas NV 89109

Long Beach, CA

Virtual

Waldorf Astoria DC (1100 Pennsylvania Avenue NW, Washington DC 20004)

Fira de Barcelona

Brussels Belgium

Javits Center

I need a column for a graphic that is parsing out City/State for inside the US and City/Country information for outside the US. (example below). Is there any way to accomplish this in Power Query?

Speech Address	State/Country
Raleigh, NC Convention Center	Raleigh, North Carolina
3799 Las Vegas Blvd, Las Vegas NV 89109	Las Vegas, Nevada
Long Beach, CA	Long Beach, California
Virtual
Waldorf Astoria DC (1100 Pennsylvania Avenue NW, Washington DC 20004)	Washington, DC, United States
Fira de Barcelona	Barcelona, Spain
Brussels Belgium	Brussels, Belgium
Javits Center	New York, New York

[lbendlin]

You can run Python and R scripts in Power Query. They need to result in a dataframe, and they require the use of a personal gateway.