Apr 08 2019 04:23 AM
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.
Apr 08 2019 05:24 AM
@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.
Apr 09 2019 12:33 AM
Apr 09 2019 01:08 AM
@kobo237cs , you are welcome