I was working with some external data where I had a list of Australia postal codes, the challenge is that there are multiple suburbs that are part of one postal code.

Below I am going to show you how I took the multiple suburb names and put them into a single row, which would then allow me to have a single postal code per row. Which would then allow me to join this to my table using a relationship in my data model.

This is what the table looked like before the transformation 

This is what the table will look like once I have completed the steps below 

How I did it  

·         I first selected my column called postcode, and then in the Transform ribbon clicked on Group By  

o     

·         I then configured the Group By as shown below.  

o      

o    What I am doing here is to group by the postcode, and then putting the remaining rows effectively into a table by using the “All Rows”  

o    This resulted in a single row per postcode  

§    

·         I then went into the Add Column ribbon and clicked on Custom Column  

o      

·         I then put in the following code into my Column name “Suburbs” 

• 
• NOTE: Even though I cannot see the column called [locality] any more in the Available columns, I can still use it in my Custom Column.

• This resulted in the following in my table
  • 
• I then clicked on Expand on the Suburbs column and selected “Extract Values”
  • 
• I then selected the delimiter to be a comma for the list of values
  • 
• I could then see my new Suburbs column with the extracted values being comma delimited
  • 
• I then removed my unwanted column called AllData and renamed the column headers to get my final result.
  • 

Conclusion 

I have shown once again how versatile Power Query is and also that very often things can be done via the GUI which makes it intuitive and easy to shape your data.

If there are any questions or comments, please leave them in the section below.

Thanks once again for reading and I hope that this will be useful to you at some point!