cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hackfifi Regular Visitor
Regular Visitor

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!

 

Duplicate Query.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Phil_Seamark
Microsoft

Re: Create Duplicate Entries based on Column Value

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

 

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Microsoft Phil_Seamark
Microsoft

Re: Create Duplicate Entries based on Column Value

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

 

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

hackfifi Regular Visitor
Regular Visitor

Re: Create Duplicate Entries based on Column Value

@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!

Microsoft Phil_Seamark
Microsoft

Re: Create Duplicate Entries based on Column Value

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

hackfifi Regular Visitor
Regular Visitor

Re: Create Duplicate Entries based on Column Value

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

 

Duplicate Query2.PNG

 

 

Microsoft Phil_Seamark
Microsoft

Re: Create Duplicate Entries based on Column Value

Hi @hackfifi

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

hackfifi Regular Visitor
Regular Visitor

Re: Create Duplicate Entries based on Column Value

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

PLRD Occasional Visitor
Occasional Visitor

Re: Create Duplicate Entries based on Column Value

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors