Forum Discussion

hackfifi's avatar
hackfifi
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 Co...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    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

     

     

Phil_Seamark's avatar
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.

hackfifi's avatar
hackfifi
Helper V
8 years ago

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's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi hackfifi

     

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

    • hackfifi's avatar
      hackfifi
      Helper V
      8 years ago

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