Develop an automated method

[RRIBEIROADM]

Develop an automated method to standardize product descriptions in columns B, C, and D. Record the calculation memory and demonstrate how it was done.

[v-pnaroju-msft]

Hi RRIBEIROADM,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[Omid_Motamedise]

Hi @RRIBEIROADM 

You can solve the problem using Table.SpplitColumn() function. For details, whatc the following video of mine.

https://youtu.be/RZgX1pLfRA8?si=QQwPDpDm4fZApoXJ

[v-pnaroju-msft]

Thankyou, @DataNinja777 for your response.

Hi RRIBEIROADM,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solution provided by @DataNinja777 to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[DataNinja777 · Accepted solution]

Hi @RRIBEIROADM ,

To automate the standardization of your product descriptions, the most effective method is using Power Query, an integrated tool within Excel. It allows you to build a repeatable transformation process that acts as a "calculation memory," which can be refreshed with a single click without needing VBA.

First, you must load your data into the Power Query Editor. Select your table of products, navigate to the Data tab on the Excel ribbon, and click on From Table/Range. This action opens the Power Query Editor, where all transformations will be performed.

Within the editor, you'll extract the required information into new columns. The most intuitive way to do this is with the Column From Examples feature, found under the Add Column tab. To extract the BRAND, start a new column from examples and type the brand name (e.g., "Ypê", "Veja") for the first few rows. Power Query will learn the pattern and auto-fill the rest. Repeat this exact process to create a SIZE column by typing examples like "500ml" and "2L". To extract the FUNCTION, select the original product description column, go to the Home tab, and use Split Column > By Delimiter. Choose a hyphen (-) as the delimiter and split at the "left-most delimiter." This will separate the product type from its function. The newly created column will contain the function description.

Finally, organize your table for the final output. Rename the columns you created to match your desired headers: TYPE OF PRODUCT/SIZE, FUNCTION, and BRAND. You may need to merge the extracted product type with the SIZE column to create the TYPE OF PRODUCT/SIZE column. Remove any intermediate or redundant columns by right-clicking them and selecting "Remove." Reorder the columns into the desired final sequence. Once complete, click Close & Load from the Home tab. This will load the standardized table into a new worksheet in Excel.

The entire process is recorded step-by-step in the Applied Steps pane within the Power Query Editor. This pane serves as the complete evidence and calculation memory of how the standardization was performed. If you add new products to your original source table, you can simply right-click the new, standardized table and select Refresh to apply all the same transformations automatically.

Best regards,