Power Query Problems

How to Combine Columns into a URL in Power Query

Maira Mehtab Sep 22, 2026 872 views

Question details

The user needs to create a Power Query expression that dynamically merges data from multiple source columns to form a complete, correctly formatted URL (such as a specific product link).

Product: Microsoft Excel
Device & OS: not provided
Scenario: Transforming imported data sets by combining baseline web addresses with unique identifiers located in separate columns to generate functional web links.
Observed behavior: The goal is to successfully concatenate static text strings and dynamic column variables to output a valid web URL within the Power Query Editor.

Before you start

Ensure your source data is formatted as an Excel Table and loaded into the Power Query Editor. Identify the base URL structure and the specific columns containing the variable data you want to append.

Solution 1Recommended

Use the Ampersand (&) Operator in a Custom Column

The most straightforward way to combine column values into a URL is by adding a custom column and using the ampersand (&) operator to concatenate text and column references.

This method functions similarly to basic Excel formulas. You construct the URL by stringing together fixed text (enclosed in quotation marks) and dynamic column names from your query.

Add a Custom Column

In the Power Query Editor, go to the 'Add Column' tab on the ribbon and click on 'Custom Column'.

Name the Column

In the Custom Column dialog box, enter a recognizable name for your new column, such as 'ProductURL'.

Write the Concatenation Formula

In the Custom column formula box, enter your expression using the & operator. For example: = "https://www.sportscardspro.com/product/" & [CategoryColumn] & "/" & [IDColumn]

Apply and Check Data Type

Click 'OK' to save the formula. Verify the new column generates the correct URL, then change its data type to 'Text'.

Formatting Numbers: If any of your source columns contain numbers, you must wrap them in the Text.From() function in your formula, like this: & Text.From([IDColumn])

Solution 2

Use the Text.Combine Function

For queries where you need to combine multiple columns and static text elements, the Text.Combine function offers a clean alternative to using multiple ampersands.

Free Microsoft Office alternative

Looking for a lightweight, free alternative for spreadsheet tasks?

While advanced Power Query tasks require Excel, WPS Office Spreadsheet provides powerful built-in text functions (like CONCATENATE and HYPERLINK) that allow you to combine columns into clickable URLs easily and completely free. Enjoy a seamless spreadsheet experience without the heavy subscription costs.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your device.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbook.
3. Use the HYPERLINK Function: Instead of Power Query, simply use a formula like =HYPERLINK("https://domain.com/" & A2, "View Product") to create a clickable URL instantly.

Fully compatible with Microsoft Excel (.xlsx) file formats.Easily create custom URLs using standard built-in text functions.Lightweight installation that runs fast on Windows, Mac, and Linux.Free to use with a familiar, easy-to-navigate user interface.

microsoft office alternative - wps office

Frequently Asked Questions

How do I handle spaces in column names when combining them into a URL in Power Query?

URLs cannot contain spaces. If your column values have spaces, you should wrap the column reference in the Uri.EscapeDataString([ColumnName]) function within your Custom Column formula. This will automatically convert spaces and special characters into URL-friendly formats (like %20).

Why am I getting a data type error when combining a number column into my URL?

Power Query requires all components of a concatenated string to be text. If you are referencing a numeric column, you must convert it to text using the Text.From() function. For example: = "https://domain.com/" & Text.From([NumberColumn]).

Why isn't my concatenated URL clickable after loading it to the Excel worksheet?

Power Query outputs the combined URL as plain text. To make it a clickable link in your worksheet, you can either double-click the cell and press Enter, or add an extra column in Excel that wraps the Power Query output in the =HYPERLINK() formula.

Excel & Data Problems