+Aggregated data in column - how to deal with it

[superspost]

Dear all, thanks for entertaining my question - novice here. I am importing a "orders" table from a web api (woocommerce) and when wanting to select the relevant data in power query, i am presented with one column (see picture 1) that has some critical data about products that were purchased in a single column. See picture 2 for a detailed view of how what the cell content looks like.

How can i go about this data? I would like to have column per product for quantity and price each. There is a total of 8 products i think.

Any pointer for a how to approach this manipulation challenge are highly apprechiated.

[v-yanjiang-msft]

Hi @superspost ,

According to your sample, all attributes of one product are separated by commas, you can split them in Power Query.

Here's my solution.

This is my sample data.

1.Select the column, then click Split Column tab under Transform ribbon, select By Delimiter.

Select Comma in the delimiter box.

Rename the column name, get this table.

2.For Quantity and  Price column, if you only want to show the number, select the column, click Extract tab under Transform ribbon, select Text After Delimiter.

Enter a colon in the Delimiter box.

Get this table.

3.For other columns, if you don't want to show them in seperate column, you can also select Merge Columns.

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[MFelix]

@v-yanjiang-msft ,

The result input data you present is not in the same format has the sample given in the images so this will create some addtional steps that will cause more problems for the transformation.

That's why I asked for a sample data because of the small things like the line breaks the parenteses and all of that needt to be adress befor making use of an option like yours.

[MFelix]

Hi @superspost ,

In this case you need to use the split by delimitor option and some other options that will allow you to make the transformation.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.