How to prepare data when there are multiple items in 1 field

[Anonymous]

Hi. I need to create a stacked bar chart of items sold per month; however, the data presented to me came with all the data in a single field. Any advice on how to get this into the correct format? 

ex.

Date                    Order #   / Item

2018-01-01         / 1           / Hamburger, Fries, Soda

2018-01-01         / 2           / Fries, Shake

2018-01-02         / 3           / Hamburger, Soda

2018-01-02         / 4           / Hamburger

On my report, I need to show that on

Jan 01, I sold 1 Hamburger, 2 Friest, 1 Soda, 1 Shake

Jan 02, I sold 2 Hamburgers, 1 Soda

Thanks!

[pawel1 · Accepted solution]

 You want your SalesTable look like this:

1. Use Query Editor to re-format your source data.  Steps:

- Split Colums by Delimiter /

- Split Columns by Delimiter ,

- Unpivot the last 3 columns (with Items)

- Adjust Data Type, etc

2. Add a measure 

Count Items = COUNTROWS(SalesTable)

3. Visualize with Matrix or other chart 

[v-chuncz-msft · Accepted solution]

@Anonymous,

By the way, you may select Split into Rows directly.

[Anonymous]

I've created the extra columns and it works...to a degree. I can't build charts that show % of Grand Total (because each column has it's own Grand Total). I tried creating a Grand Total of all items sold; however, I can't seem to get a % of Grand Total view still (could be a simple calc that I'm missing).  My example was very simple. What if instead of months, it was a unique reference number (order #)?  e.g. 10001, 10002, 10003 and the item sold had various items in the one field?  And there millions of orders generated monthly?  Thanks!

[pawel1 · Accepted solution]

 You want your SalesTable look like this:

1. Use Query Editor to re-format your source data.  Steps:

- Split Colums by Delimiter /

- Split Columns by Delimiter ,

- Unpivot the last 3 columns (with Items)

- Adjust Data Type, etc

2. Add a measure 

Count Items = COUNTROWS(SalesTable)

3. Visualize with Matrix or other chart 

[elliotdixon]

@pawel1this worked great.
I was missing the upivot columns - cheers!