Do I need to split a row to graph unique values

[M_Capps]

Good day,

I have the following table that tracks orders and the category(ies) that the items belong in:

Order Number

Item Number

Agency

County

Category 1

Category 2

Category 3

Category 4

Category 5

Category 6

Category 7

Category 8

Category 9

Category 10

Category 11

Category 12

201905378

2-1

ACME, Inc.

New Hanover

Y

Y

201905378

2-1

ACME, Inc.

New Hanover

Y

201905378

2-2

ACME, Inc.

New Hanover

Y

Y

201917154

2

JustAnotherPlace

Craven

Y

201917154

3

JustAnotherPlace

Craven

Y

202000456

1

Knowhere

Craven

Y

Y

202000456

1

Knowhere

Craven

Y

202002107

1

Places

Pasquotank

Y

Y

Y

202002345

1

NothingKnew

Jones

Y

Y

Y

202002818

1

AlwaysOld

Pamlico

Y

Y

On my visualization, I need this table as-is but also need a Stacked Bar Chart that graphs the data by Agency and Category:

When an item has only one category, it graphs correctly. However, when an order has multiple categories for the same item (Order Number 202002345) the graph only picks up the last indicated category. Order Number 202002345 only counts Category 8 and ignores Category 2 and Category 4.

The actual table is roughly 25,000 rows so manually splitting isn't feasible.  Is there a way to dynamically code it where I actually can catch multiple categories for the same Order Number and Item.

Thanks

[Ritaf1983 · Accepted solution]

Hi @M_Capps 

To work effectively with the data in the table, it is necessary to unpivot it.
Don't worry 25K rows is a very small database for a power bi engine.

The steps for unpivot on power query:
1. Select all columns except of categories

2. Use the option of "unpivot other columns"

3. close and apply.

4. Create the graph:

Link to the sample file 

Please consider Accepting it as the solution to help the other members find it more quickly

[M_Capps]

This did exactly what I was needing it to do. Thanks @Ritaf1983 

[Ritaf1983]

Glad to help 🙂