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

Question

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|----------------------------------|&nbsp; ww125ex |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; |----------------------------------The result I would like to have is the following:&nbsp;-----------------------------------|Incident ID&nbsp; | Number of Times|-----------------------------------| ww125ex&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; || ww125ex&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; || ww125ex&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; || ww125ex &nbsp;&nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;Can anyone suggest something to help me achieve this?Thank you a lot.

sergeibaklan · Answer

kobo237cs&nbsp;, 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.

kobo237cs · Answer

SergeiBaklan&nbsp;&nbsp;You are amazing. This worked like a charm. Thank you.

sergeibaklan · Answer

kobo237cs&nbsp;, you are welcome

Forum Discussion

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

3 Replies

Resources