Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.thedatacity.com/llms.txt

Use this file to discover all available pages before exploring further.

​
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
  1. 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)
image png Jul 15 2025 03 43 20 0848 PM 2a. Ensure Excel has promoted the headers for you
  • Sometimes Excel will do this for you but you need to promote the headers so we can used named columns.
image png Sep 29 2025 08 57 11 7148 AM 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
image png Jul 15 2025 03 45 50 6124 PM
  1. 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
image png Aug 26 2025 10 02 58 6848 AM
  1. 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.)
  1. Clean up and load
  • Remove intermediate columns
  • Close & Load to get your final table