Split rows into columns without delimiter

[Syndicate_Admin]

Hello group!

I have a column (Information) that I import from an excel file where 3 data are displayed as follows:

Information	Value
Familia1	500
SubFamilia1	300
Producto1	50
Producto2	250
SubFamilia2	200
Producto3	200
Familia2	500
SubFamilia3	300
Producto4	300
SubFamilia4	200
Producto5	100
Producto6	100

And I want to transform it into 3 separate columns:

Family	Subfamily	Product	Value
Familia1	SubFamilia1	Producto1	50
Familia1	SubFamilia1	Producto2	250
Familia1	SubFamilia2	Producto3	200
Familia2	SubFamilia3	Producto4	300
Familia2	SubFamilia4	Producto5	100
Familia2	SubFamilia4	Producto6	100

How can I do this transformation in Power Query?

Thanks a lot.

[ichavarria · Accepted solution]

Hi @Syndicate_Admin,

You can use Power Query to transform the data from the single column to multiple columns. Here's how:

1. Open Power BI and create a new query by clicking on "Get Data" in the Home tab.

2. Select the Excel file containing the data and click on "Transform Data".

3. In the Power Query Editor, select the "Information" column and click on "Split Column" in the "Transform" tab.

4. Choose "By Delimiter" and enter the delimiter as a Tab character (\t).

5. Choose "Split into Rows" and click OK.

6. Now you should have a new column with the separate values. Rename the column to "Category" by right-clicking on the column header and choosing "Rename".

7. Create a new column by clicking on "Add Column" in the "Add Column" tab.

8. Enter the following formula in the formula bar:

= if Text.StartsWith([Category], "Familia") then [Category] else null

1. Name the new column "Family".

2. Create another new column and enter the following formula:

= if Text.StartsWith([Category], "SubFamilia") then [Category] else null

1. Name the new column "Subfamily".

2. Create a third new column and enter the following formula:

= if Text.StartsWith([Category], "Producto") then [Category] else null

1. Name the new column "Product".

2. Delete the "Category" column by right-clicking on the column header and choosing "Remove".

3. Select all the columns (Family, Subfamily, Product, and Value) by clicking on the first column header and holding down the Shift key while clicking on the last column header.

4. Click on "Remove Other Columns" in the "Home" tab.

5. Close and apply the changes by clicking on "Close & Apply" in the "Home" tab.

Your data should now be transformed into the desired format with separate columns for Family, Subfamily, Product, and Value.

Best regards, 

Isaac Chavarria 


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Syndicate_Admin]

Thanks @Syndicate_Admin, great job!