Power Query Problems

How to Convert Comma-Separated Tags into TRUE/FALSE Columns in Power Query

Adam Davis Oct 8, 2026 868 views

Question details

The user needs to transform a single column of comma-separated tags into multiple individual columns (one for each tag) that display a boolean TRUE or FALSE value.

Convert Comma-Separated Tags into TRUE or FALSE Columns using Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cleaning and structuring tag-based text data for detailed analysis.
Observed behavior: Tags are currently grouped together in a single cell separated by commas, preventing independent tag analysis.

Before you start

Ensure your dataset is formatted as a recognized Table in Excel before attempting to load it into the Power Query Editor.

Solution 1Recommended

Split Tags by Delimiter and Pivot with Custom Aggregation

This solution involves splitting the comma-separated values into separate rows, and then pivoting those rows back into columns using a custom M code condition to generate TRUE and FALSE values.

By utilizing Power Query's built-in transformation tools alongside a small adjustment in the Advanced Editor, you can easily reshape your data into a boolean matrix.

Load Data into Power Query

Select your table in Excel, go to the 'Data' tab, and click 'From Table/Range' to open the Power Query Editor.

Split the Column into Rows

Select the 'Tags' column. Navigate to the 'Transform' tab, click 'Split Column', and choose 'By Delimiter'. Select comma as the delimiter. Under 'Advanced options', ensure you select 'Split into Rows' and click OK.

Pivot the Split Column

With the newly split 'Tags' column still selected, go to the 'Transform' tab and click 'Pivot Column'. In the dialog box, set the Values Column to 'Tags' (or your preferred identifier). Expand 'Advanced options' and choose 'Count (All)' as the Aggregate Value Function.

Modify the M Code for TRUE/FALSE

Open the 'Advanced Editor' from the Home tab. Locate the Table.Pivot step in the code. Replace the default counting function with `each List.Count(_) > 0`. This custom aggregation will return TRUE if the tag exists and FALSE otherwise. Click 'Done' to apply the changes.

Direct M Code Approach: If you prefer, you can paste the complete M code provided in the original solution directly into the Advanced Editor, ensuring you replace 'Table1' with your actual source table name.

Free Microsoft Office alternative

Looking for a Lightweight, Reliable Office Alternative?

While advanced Power Query scripting (M code) is natively tied to Microsoft Excel, WPS Office provides a fully featured, lightweight, and free alternative for your everyday spreadsheet, data analysis, and formatting needs.

1. Download WPS Office: Visit the official WPS Office website and download the free installer for your operating system.
2. Install the Application: Run the setup file and follow the quick installation prompts to set up WPS Office on your computer.
3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx files directly to continue your data analysis without formatting loss.

Completely free and lightweight Office suiteExcellent compatibility with Microsoft Excel (.xlsx, .xls) formatsFamiliar spreadsheet interface for zero-learning-curve migrationBuilt-in text-to-columns and data transformation utilities

Frequently Asked Questions

Can I extract TRUE/FALSE tags using standard Excel formulas instead of Power Query?

Yes. If you know the specific tags in advance, you can create new columns manually and use a formula like `=ISNUMBER(SEARCH("YourTag", A2))` to return TRUE or FALSE based on whether the tag is found in the text string.

What if my tags are separated by a space or semicolon instead of a comma?

When performing the 'Split Column by Delimiter' step in Power Query, simply change the delimiter from a comma to a semicolon, space, or custom character depending on your dataset.

Why am I getting errors when splitting the column into rows?

Errors during a split operation often occur due to inconsistent data types or blank/null values in the source column. It is recommended to apply a 'Trim' transformation and filter out or replace 'null' values before splitting.

Excel & Data Problems