I need help to produce duplicated rows the number o which is defined by a column value

[kobo237cs]

Hello, I have a specific problem to solve. I need to produce multiple rows with same attributes and the number of those rows to correspond to a value from a column in the same row.

I tried to accomplish this using the Power Query editor of Excel but to no avail.

In other words, I want to disaggregate a value from a row as if it was aggregated by pivoting the table in Excel.

For example let's say I have this row in an Excel table:

----------------------------------

|Incident ID | Number of Times|

----------------------------------
|  ww125ex |           4                |

----------------------------------

The result I would like to have is the following:

-----------------------------------

|Incident ID  | Number of Times|

-----------------------------------

| ww125ex    |           1               |

| ww125ex    |           1               |

| ww125ex    |           1               |

| ww125ex    |           1               |

Can anyone suggest something to help me achieve this?

Thank you a lot.

[#2]

@kobo237cs , to transform first table to the second with Power Query

you may first add custom column with

List.Repeat({[Incident ID]},[Number of Times])

to repeat your texts into the list, and after that expand that list into the rows. Entire script is like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddListOfTexts = Table.AddColumn(Source, "Incident", each List.Repeat({[Incident ID]},[Number of Times])),
    ExpandList = Table.ExpandListColumn(AddListOfTexts, "Incident"),
    AddOne = Table.AddColumn(ExpandList, "Number", each 1),
    RemoveUnused = Table.SelectColumns(AddOne,{"Incident", "Number"})
in
    RemoveUnused

and in attached file.

[#3]

@Sergei Baklan 

You are amazing. This worked like a charm. Thank you.

[#4]

@kobo237cs , you are welcome