Importing irregular group data?

[mondegren]

I'm new Power BI and am trying to import data that is irregular.  Specifically:

Date | Order# | Attribute group where there are 3 attributes that can repeat 0 to many times (e.g. Color, Size, Quantity)

I'm trying to get the data so that it would fit into a structured table like:

Date | Order# | Color | Size | Quantity

For an example, if I have the following data:

6/27/17 | 00001 | Red, Large, 3

6/27/17 | 00002 | Blue, Medium, 2 | Yellow, Medium, 4 | Green, Small, 1

I'm trying to transform it to:

Date | Order# | Color | Size | Quantity

6/27/17 | 00001 | Red | Large | 3

6/27/17 | 00002 | Blue | Medium | 2

6/27/17 | 00002 | Yellow | Medium | 4

6/27/17 | 00002 | Green | Small | 1

I've seen various posts and tried to un-pivot the data, but that doesn't seem to work.  Any thoughts on what I need to do?

[Vvelarde · Accepted solution]

@mondegren

Hi, please follow the steps in the image below

Regards

Victor

Lima - Peru

[mondegren]

Thanks @Vvelarde!! This works great!

I had been splitting the grouped data first and then trying to un-pivot.  The trick is to unpivot the GROUPED data, and THEN split the grouped data into the regular columns.

Thanks again!

Mondegren