Explanation:
Box 1: By delimiter
In the Power Query Editor, you can use the Split Column by Delimiter feature and its Advanced options to split a column of delimited values into multiple rows.
Box 2: Set Split into to Rows
The following process results in a table where each row contains a single, unique combination of Product Category and ProductID.
Steps to Split a Column into Rows using Power Query
1. Select the Column: In the Power Query Editor, click the header of the "ProductIDList" column to select it.
2. Access Split Column: Go to the Home tab, click the drop-down arrow next to Split Column, and select By Delimiter. (Alternatively, you can right-click the column header and choose Split Column > By Delimiter).
*-> 3. Configure the Delimiter: The ProductIDValues values in your example are inside curly braces and separated by a comma and a space (e.g., {1, 4, ...}).
3a. In the Split a column by delimiter dialog box, select or enter the delimiter used to separate the IDs. In this case, you'll need to use a Custom delimiter of a comma followed by a space (, ).
3b. You should also specify to split at Each occurrence of the delimiter.
*-> 4. Set Split into Rows: Crucially, under the Advanced options, select the Rows option in the Split into dropdown menu. The default is typically Columns, so switching to Rows is the key step to achieve your desired output.
5. Confirm: Click OK to apply the transformation. Power Query will expand each ProductID value into its own row, automatically repeating the corresponding Product Category value for each new row.
Reference:
https://learn.microsoft.com/en-us/power-query/split-columns-delimiter