Create Duplicate Entries based on Column Value

[hackfifi]

Good Day - I have a table in PowerBI Query as DATA [shown below]

I am trying to convert that DATA to OUTPUT [shown below]

In summary, Each ROW needs to be duplicated based on COUNT value, and ID Column needs to be added for that set of duplicate data.

Appreciate any help to make this possible!

[Phil_Seamark · Accepted solution]

Hi @hackfifi

You could try this calculated table in DAX

Table 2 = 
    GENERATE(
        'Table',
         FILTER(
             SELECTCOLUMNS(
                 GENERATESERIES(1,100),
                 "ID",[Value]
                 ),
                 [ID]<=[Count])
                 )

This gave me this result

[PLRD]

I need to do the same thing but in Power Query. Any idea how?

[hackfifi]

@Phil_Seamark - Mate you never fail to disappoint!

Couple of Queries:

1. Using the Generate Table function, i assume now i cannot accesss the new generated table via Query Editor to make some changes?

2. I am trying to understand your formular - what does "GenerateSeries(1,100) do?

Thanks again!

[Phil_Seamark]

Hi @hackfifi

The GenerateSeries function just creates a single column table with values between 1 and 100

if you create a new calculated table and use just that function you will see what it looks like.

I wrapped it with the SELECTCOLUMNS function, simply to rename the column as you already had a column called [Value] in your first column.

[hackfifi]

Ok @Phil_Seamark - So i am doing SOMETHING wrong. Obviously the below the "REAL" data, not the SAMPLE table i provided earlier. As you can see each row has count value of "19" in this case. Hence  the generated table should show each row 19 times i.e. it should have 57 rows of data

[Phil_Seamark]

Hi @hackfifi

Make sure the data type of your [Count] column is set to int.  Try that...

[hackfifi]

Yes the "Count" column on Main Table is of Type "Whole Number"....
Maybe i will import the data and start again