Forum Discussion
Split rows into columns without delimiter
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.
Hi Syndicate_Admin,
You can use Power Query to transform the data from the single column to multiple columns. Here's how:
Open Power BI and create a new query by clicking on "Get Data" in the Home tab.
Select the Excel file containing the data and click on "Transform Data".
In the Power Query Editor, select the "Information" column and click on "Split Column" in the "Transform" tab.
Choose "By Delimiter" and enter the delimiter as a Tab character (\t).
Choose "Split into Rows" and click OK.
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".
Create a new column by clicking on "Add Column" in the "Add Column" tab.
Enter the following formula in the formula bar:
= if Text.StartsWith([Category], "Familia") then [Category] else null
Name the new column "Family".
Create another new column and enter the following formula:
= if Text.StartsWith([Category], "SubFamilia") then [Category] else null
Name the new column "Subfamily".
Create a third new column and enter the following formula:
= if Text.StartsWith([Category], "Producto") then [Category] else null
Name the new column "Product".
Delete the "Category" column by right-clicking on the column header and choosing "Remove".
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.
Click on "Remove Other Columns" in the "Home" tab.
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
2 Replies
Hi Syndicate_Admin,
You can use Power Query to transform the data from the single column to multiple columns. Here's how:
Open Power BI and create a new query by clicking on "Get Data" in the Home tab.
Select the Excel file containing the data and click on "Transform Data".
In the Power Query Editor, select the "Information" column and click on "Split Column" in the "Transform" tab.
Choose "By Delimiter" and enter the delimiter as a Tab character (\t).
Choose "Split into Rows" and click OK.
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".
Create a new column by clicking on "Add Column" in the "Add Column" tab.
Enter the following formula in the formula bar:
= if Text.StartsWith([Category], "Familia") then [Category] else null
Name the new column "Family".
Create another new column and enter the following formula:
= if Text.StartsWith([Category], "SubFamilia") then [Category] else null
Name the new column "Subfamily".
Create a third new column and enter the following formula:
= if Text.StartsWith([Category], "Producto") then [Category] else null
Name the new column "Product".
Delete the "Category" column by right-clicking on the column header and choosing "Remove".
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.
Click on "Remove Other Columns" in the "Home" tab.
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 quicklyThanks @Syndicate_Admin, great job!
