Expand a complete list of items from a summarized one

[Cedbont]

Hi there!

I have got a "summarized" list of items in Excel stating:

• The type of item: A, B, C...
• The number of categories of each item: 1 to N
• The number of subcategories of each item (that is identical in each category of the said item): 1 to M.

Here is an example:

I would like to get the complete expanded list of items looking like the following:

Does anyone know how to do that?

Thanks in advance!

Cedbont

[slorin]

@Cedbont 

Pourquoi vouloir utiliser List.Generate alors que {1..[Column2]} fonctionne bien ?
List.Reverse au besoin pour l'ordre inverse.

Stéphane

[Cedbont]

Hi,

Because I struggled. Now I got it.

Thanks!

Cedbont.

[HotChilli · Accepted solution]

Create 2 columns in Power Query using the column value to create a list in each:

First one  {1..[Number of Categories]}

Second one {1..[Number of Subcategories]}

--

remove the category and subcat columns.

Expand the list columns from the header (into New Rows)

[Cedbont]

Hi,

I partly succeeded in doing what you advised.

Here is my code:

= Table.AddColumn(#"Type modifié", "Personnalisé", each List.Generate( () => [Column2] , each _ >= 1 , each _ -1 ) )

= Table.ExpandListColumn(#"Personnalisée ajoutée", "Personnalisé")

Here is the result:

You notice ranks are deacreasing in the 4th column (as requested in the list.generate function).

I tried to have it increasing without success. Here is my code and the error message:

= Table.AddColumn(#"Type modifié", "Personnalisé", each List.Generate( () => 1 , each _ <= [Column2] , each _ +1 ))

= Table.ExpandListColumn(#"Personnalisée ajoutée", "Personnalisé")

I do not see where I have it wrong... Would someone mind to help me?

Thank you

Cedbont