Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-405998%22%20slang%3D%22en-US%22%3EI%20need%20help%20to%20produce%20duplicated%20rows%20the%20number%20o%20which%20is%20defined%20by%20a%20column%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-405998%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20a%20specific%20problem%20to%20solve.%20I%20need%20to%20produce%20multiple%20rows%20with%20same%20attributes%20and%20the%20number%20of%20those%20rows%20to%20correspond%20to%20a%20value%20from%20a%20column%20in%20the%20same%20row.%3C%2FP%3E%3CP%3EI%20tried%20to%20accomplish%20this%20using%20the%20Power%20Query%20editor%20of%20Excel%20but%20to%20no%20avail.%3C%2FP%3E%3CP%3EIn%20other%20words%2C%20I%20want%20to%20disaggregate%20a%20value%20from%20a%20row%20as%20if%20it%20was%20aggregated%20by%20pivoting%20the%20table%20in%20Excel.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFor%20example%20let's%20say%20I%20have%20this%20row%20in%20an%20Excel%20table%3A%3C%2FP%3E%3CP%3E----------------------------------%3C%2FP%3E%3CP%3E%7CIncident%20ID%20%7C%20Number%20of%20Times%7C%3C%2FP%3E%3CP%3E----------------------------------%3CBR%20%2F%3E%7C%26nbsp%3B%20ww125ex%20%7C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%204%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E----------------------------------%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThe%20result%20I%20would%20like%20to%20have%20is%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-----------------------------------%3C%2FP%3E%3CP%3E%7CIncident%20ID%26nbsp%3B%20%7C%20Number%20of%20Times%7C%3C%2FP%3E%3CP%3E-----------------------------------%3C%2FP%3E%3CP%3E%7C%20ww125ex%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E%7C%20ww125ex%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E%7C%20ww125ex%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E%7C%20ww125ex%20%26nbsp%3B%26nbsp%3B%20%7C%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20suggest%20something%20to%20help%20me%20achieve%20this%3F%3C%2FP%3E%3CP%3EThank%20you%20a%20lot.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-405998%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-406566%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20help%20to%20produce%20duplicated%20rows%20the%20number%20o%20which%20is%20defined%20by%20a%20column%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-406566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F315893%22%20target%3D%22_blank%22%3E%40kobo237cs%3C%2FA%3E%26nbsp%3B%2C%20to%20transform%20first%20table%20to%20the%20second%20with%20Power%20Query%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20368px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F106091i838645BE33913EFD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20first%20add%20custom%20column%20with%3C%2FP%3E%0A%3CPRE%3EList.Repeat(%7B%5BIncident%20ID%5D%7D%2C%5BNumber%20of%20Times%5D)%3C%2FPRE%3E%0A%3CP%3Eto%20repeat%20your%20texts%20into%20the%20list%2C%20and%20after%20that%20expand%20that%20list%20into%20the%20rows.%20Entire%20script%20is%20like%3C%2FP%3E%0A%3CPRE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20AddListOfTexts%20%3D%20Table.AddColumn(Source%2C%20%22Incident%22%2C%20each%20List.Repeat(%7B%5BIncident%20ID%5D%7D%2C%5BNumber%20of%20Times%5D))%2C%0A%20%20%20%20ExpandList%20%3D%20Table.ExpandListColumn(AddListOfTexts%2C%20%22Incident%22)%2C%0A%20%20%20%20AddOne%20%3D%20Table.AddColumn(ExpandList%2C%20%22Number%22%2C%20each%201)%2C%0A%20%20%20%20RemoveUnused%20%3D%20Table.SelectColumns(AddOne%2C%7B%22Incident%22%2C%20%22Number%22%7D)%0Ain%0A%20%20%20%20RemoveUnused%3C%2FPRE%3E%0A%3CP%3Eand%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-414402%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20help%20to%20produce%20duplicated%20rows%20the%20number%20o%20which%20is%20defined%20by%20a%20column%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-414402%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20amazing.%20This%20worked%20like%20a%20charm.%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-414668%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20help%20to%20produce%20duplicated%20rows%20the%20number%20o%20which%20is%20defined%20by%20a%20column%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-414668%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F315893%22%20target%3D%22_blank%22%3E%40kobo237cs%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
kobo237cs
New Contributor

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.

3 Replies

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

image.png

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.

@Sergei Baklan 

 

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies