Help in Transpose

[tahertalib]

Hi All,

I am working with google API and have got the below output(Google API output from Alteryx). I want to transpose that data into the below format. Could someone please help me with the same?

the JSON name output from google is dynamic. If there is a suite number attached to it, then the value shifts. Any help will be appreciated.

As an alternative, can i use the data from column "result.adr_address" with REGEX to split the address into separate fields?

Expected Output from below google API:

street number	route	locality	administrative_area_level_2	administrative_area_level_1	country	postal_code	result.geometry.location.lat	result.geometry.location.lng
311	Burnt Ridge Rd	Blackfalds	Red Deer County	AB	CA	T0M 0J0	52.2935953	-113.8694788

Google API output from Alteryx

JSON_Name	JSON_ValueString
result.address_components.0.long_name	12345
result.address_components.0.short_name	12345
result.address_components.0.types.0	street_number
result.address_components.1.long_name	Burnt Ridge Road
result.address_components.1.short_name	Burnt Ridge Rd
result.address_components.1.types.0	route
result.address_components.2.long_name	Blackfalds
result.address_components.2.short_name	Blackfalds
result.address_components.2.types.0	locality
result.address_components.2.types.1	political
result.address_components.3.long_name	Red Deer County
result.address_components.3.short_name	Red Deer County
result.address_components.3.types.0	administrative_area_level_2
result.address_components.3.types.1	political
result.address_components.4.long_name	Alberta
result.address_components.4.short_name	AB
result.address_components.4.types.0	administrative_area_level_1
result.address_components.4.types.1	political
result.address_components.5.long_name	Canada
result.address_components.5.short_name	CA
result.address_components.5.types.0	country
result.address_components.5.types.1	political
result.address_components.6.long_name	T1M 0J0
result.address_components.6.short_name	T1M 0J0
result.address_components.6.types.0	postal_code
result.adr_address	<span class="street-address">12345 Burnt Ridge Rd</span>, <span class="locality">Blackfalds</span>, <span class="region">AB</span> <span class="postal-code">T1M 0J0</span>, <span class="country-name">Canada</span>
result.formatted_address	12345 Burnt Ridge Rd, Blackfalds, AB T1M 0J0, Canada
result.geometry.location.lat	11111
result.geometry.location.lng	22222

[Chantelb]

Hi @tahertalib ,

You can make it dynamic by using types.0 to name the columns. If there is a suite number it will have a new column which you can deselect if you wish. See workflow attached.

Let me know if this helps.

[tahertalib]

Thanks @Chantelb 

The solution works perfect. Had another query. If i try to replicate this for multiple addresses, it doesnt work as expected. Would you know how to deal with this if there are 100's of addresses? 

Thanks

[Chantelb]

Hi @tahertalib ,

Yes you need a number for each address and then you can use this to group by on the cross-tab tools. If there is no number from the API you can create one using a multi-row formula. See updated workflow attached.

Cheers