Reverting downloads to old locations format

​

Converting Company Data from New Format to Old Format

​

Overview

• Companies tab: Contains company information (Companynumber, Companyname, etc.)
• Locations tab: Contains location data (Companynumber, Postcode, LAname, LAcode, etc.)

​

Steps

1. Prepare your data

• Download the data from the platform and ensure both Companies and Locations tabs exist
• Open a blank Excel file

2. Load data into Power Query

• Go to Data > Get Data > From Other Sources > From Workbook
• Select your Excel file you’ve just downloaded and choose both tables
• Load them into Power Query Editor by selecting Transform Data (bottom right)

• Sometimes Excel will do this for you but you need to promote the headers so we can used named columns.

• In Power Query Editor, select the Companies table
• Go to Home > Combine > Merge Queries as New
• Select Locations table as the second table
• Join on Companynumber column from both tables
• Choose Left Outer join type

4. Create concatenated columns

• In the ribbon, click Add Column tab
• Click Custom Column
• New column name: Type “LAname_Combined”
• Custom column formula: Copy and paste this exactly:

Text.Combine([Locations][LAname], ", ")

• Click OK

5. Repeat Step 4 as necessary

• For LAcode data, click Add Column > Custom Column
• New column name: Type “LAcode_Combined”
• Custom column formula:
  Text.Combine([Locations][LAcode], ", ")
  
• Click OK
• For Postcode data, click Add Column > Custom Column
• New column name: Type “Postcode_Combined”
• Custom column formula:
  Text.Combine([Locations][Postcode_withspaces], ", ")
  
• Click OK
• Repeat for any other location columns you need (Constituencies, ITL regions, etc.)

6. Clean up and load

• Remove intermediate columns
• Close & Load to get your final table