Forum Discussion

Helper V

8 years ago

Solved

Create Duplicate Entries based on Column Value

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!

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

7 Replies

Phil_Seamark
Microsoft Employee
8 years ago
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
- hackfifi
  Helper V
  8 years ago
  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
    Microsoft Employee
    8 years ago
    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.
- PLRD
  New Member
  7 years ago
  I need to do the same thing but in Power Query. Any idea how?

Related content