adding new rows based on column value

[khabouri]

hi there
well yesterday i had add power query for my Microsoft excel 2010, so i am really new to this. so my question is that power query can add columns, but no rows? unless if we add column then we pivot it, but even adding columns has to be manual, i mean i dont know yet how to make an automatic formula.
in my scenario i have the following data

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Description[/TD]
[TD]type[/TD]
[TD]cat.[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]A-001[/TD]
[TD]alpha[/TD]
[TD]a[/TD]
[TD]cat.1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C-003[/TD]
[TD]delta[/TD]
[TD]a[/TD]
[TD]cat.2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C-004[/TD]
[TD]beta[/TD]
[TD]c[/TD]
[TD]cat.1[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

now the "number" represents how many time is data copied for the represented row.
so what i am trying to achieve first is as follow:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Description[/TD]
[TD]type[/TD]
[TD]cat.[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]A-001[/TD]
[TD]alpha[/TD]
[TD]a[/TD]
[TD]cat.1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C-003[/TD]
[TD]delta[/TD]
[TD]a[/TD]
[TD]cat.2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C-003[/TD]
[TD]delta[/TD]
[TD]a[/TD]
[TD]cat.2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C-004[/TD]
[TD]beta[/TD]
[TD]c[/TD]
[TD]cat.1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C-004[/TD]
[TD]beta[/TD]
[TD]c[/TD]
[TD]cat.1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C-004[/TD]
[TD]beta[/TD]
[TD]c[/TD]
[TD]cat.1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C-004[/TD]
[TD]beta[/TD]
[TD]c[/TD]
[TD]cat.1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]C-004[/TD]
[TD]beta[/TD]
[TD]c[/TD]
[TD]cat.1[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

so is there a way to go to power query advance and add a formula to make this automatically?

thank you in advance and best regards
Khabouri

 

[MarcelBeug]

After 1 day experience with Power Query it is way too soon to draw conclusions about what would be (im)possible.
Please bear in mind that Power Query is built on a complete programming language ("M"), so just assume for now that anything is possible until proven otherwise.

I created table Data in Excel and the query below for your requirements. You can create a new blank query, go into the Advanced Editor and replace the default code with the code below.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Description", type text}, {"type", type text}, {"cat.", type text}, {"number", Int64.Type}}),
    TransformedToLists = Table.TransformColumns(#"Changed Type", {{"number", each {1.._}, type {Int64.Type}}}),
    #"Expanded number" = Table.ExpandListColumn(TransformedToLists, "number")
in
    #"Expanded number"