Microsoft Fabric  

How to Split Columns Easily in Fabric Dataflow Gen2

When preparing data for reporting or analytics, one of the most common transformations you’ll encounter is the need to split a column into multiple columns. For example, a dataset may contain a single field with values separated by delimiters like commas, semicolons, or spaces. To make the data more structured and useful, these values often need to be broken down into separate fields.

With Microsoft Fabric Dataflow Gen2, splitting columns becomes simple, efficient, and scalable, enabling you to clean and reshape data before loading it into your Lakehouse, Warehouse, or downstream Power BI models.

Why Split Columns?

Data often arrives in unstructured or semi-structured formats. Consider a scenario where your dataset has a column called Skills containing values like:

Excel;Power BI;SQL
Python;R;Tableau
Azure;Databricks;Snowflake

If you want to analyze which skill appears most frequently, having them lumped into a single column isn’t helpful. By splitting the column, each skill gets its own field, allowing for richer transformations, aggregations, and visualizations.

Split Columns in Fabric Dataflow Gen2

  1. Open Dataflow Gen2

    • Navigate to your Fabric workspace.

    • Create or open an existing Dataflow Gen2.

  2. Connect to Your Data Source

    • Choose your source, such as a CSV file in OneLake, an Excel workbook, or a SQL table.

    • Load the data into the Power Query Online editor (which powers Dataflow Gen2).

      2

  3. Select the Column to Split

    • In the Power Query editor, highlight the column that contains the combined values (e.g., Skills).

  4. Apply Split Transformation

    • Go to the Transform tab.

    • Click Split Column → choose the appropriate option:

      3

      • By Delimiter – ideal for cases where values are separated by ,, ;, or another character.

        5

  5. Configure the Split

    • For example, if splitting by a semicolon (;), select Split by Delimiter → choose Each occurrence of the delimiter.

    • The column will expand into multiple new columns: Skills.1, Skills.2, Skills.3, and so on.

      6

  6. Rename the Columns

    • Rename each new column to something meaningful, such as Skill 1, Skill 2, Skill 3.

  7. Save and Load

    • Once your transformations are complete, click Save & Close.

    • Dataflow Gen2 will process the data and make it available for downstream use in Power BI or Fabric Lakehouse.

Conclusion

Splitting columns in Fabric Dataflow Gen2 is a powerful yet straightforward transformation that helps bring structure and clarity to your datasets. Whether you’re separating skills, parsing locations, or breaking down product codes, this feature ensures your data is ready for analysis in Power BI or downstream Fabric services.

By mastering transformations like Split Column, you’ll streamline data preparation workflows and deliver more reliable insights to your business.

Membership not found