Converting Company Data from New Format to Old Format
What you'll get: One row per company with all location names combined in a single column, separated by commas.
Time required: 5-10 minutes
Overview
Your download now comes in two separate tabs:- Companies tab: Contains company information (Companynumber, Companyname, etc.)
- Locations tab: Contains location data (Companynumber, Postcode, LAname, LAcode, etc.)
This guide uses Excel's Power Query to combine these into the old single-row format where each company has all its location data in one concatenated column.
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)
3. Join the tables
- 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(List.Distinct([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(List.Distinct([Locations][LAcode]), ", ")- Click OK
- For Postcode data, click Add Column > Custom Column
- New column name: Type "Postcode_Combined"
- Custom column formula:
Text.Combine(List.Distinct([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