Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bjackson
Regular Visitor

Pivot after unpivot not working

Hi there! I have a source table that looks like this: 

 

Source Table: 

Contact NameAddress 1 - CityAddress 1 - CountryAddress 2 - CityAddress 2 - Country
JohnAntofagastaChileBrisbaneAustralia
JanePerthAustraliaSantiagoChile

 

As you can see, each contact have multiple addresses (Address 1 and Address 2) and every address have more than one attribute (City and Country). All of this expressed as columns. 

 

I need to transform the previous table so I can get the addresses columns as rows:

 

Desired Table:

Contact NameCityCountry
JohnAntofagastaChile
JohnBrisbaneAustralia
JanePerthAustralia
JaneSantiagoChile

 

I tried unpivoting all the addresses columns from my source touble, but from there, I don't know how to get to my desired table:

Step 1 - Unpivot Source Table:

Contact NameAttributeValue
JohnAddress 1 - CityAntofagasta
JohnAddress 1 - CountryChile
JohnAddress 2 - CityBrisbane
JohnAddress 2 - CountryAustralia
JaneAddress 1 - CityPerth
JaneAddress 1 - CountryAustralia
JaneAddress 2 - CitySantiago
JaneAddress 2 - CountryChile

 

Step 2 - Split, trim and clean "Attribute" column so I get only the attributes of the address:

Contact NameAttributeValue
JohnCityAntofagasta
JohnCountryChile
JohnCityBrisbane
JohnCountryAustralia
JaneCityPerth
JaneCountryAustralia
JaneCitySantiago
JaneCountryChile

 

Step 3 - Pivot back "Attribute" column so I can get "City" and "Country" as columns and all the attributes values as rows.

 

This last step is the one I can't make it work. 

How can I do it? 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

You would need to keep the (1,2 ) details for the last step to work correctly.

It is possible but I would probably advise a simpler approach:

Duplicate the original table.

In one query remove the (2) columns.

In the other remove the (1) columns.

Edit the column names to be the same in both queries.

Append the queries.

--

If it is important to hold on to the 1,2 details then add a column in each query that identifies the AddressNo (hardcode it to 1 or 2 for the appropriate table)

 

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

You would need to keep the (1,2 ) details for the last step to work correctly.

It is possible but I would probably advise a simpler approach:

Duplicate the original table.

In one query remove the (2) columns.

In the other remove the (1) columns.

Edit the column names to be the same in both queries.

Append the queries.

--

If it is important to hold on to the 1,2 details then add a column in each query that identifies the AddressNo (hardcode it to 1 or 2 for the appropriate table)

 

I splitted the column Attribute by delimiter equals to "-" resulting in two columns: Column "Attribute.1" with values "Address 1" and "Address 2" and column "Attribute.2" with values "City" and "String". 
After doing some string transformation on the "Attribute.1" column to keep just the number (1 and 2) and then renamining the column to "Address #", I was finally able to pivot the column "Attribute.2" and get the desired table. Thanks for your help! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors