Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Power_BI_new
Frequent Visitor

Duplicate rows in Power Query

Good morning 🙂,

I have a database with steps applied to it on Power Query, I would like to add one last step to:
duplicate all the rows of my table whose "Account number" column begins with 6 or 7. I'll give you an example of what I want with a before and after:

 

Code Category Account numberData 
1600000E
3B700000D

3

C100000C

4

E200000G


Desired result : 

Code Category Account numberData 
1600000E
3B700000D

3

C100000C

4

E200000G
1600000E
3B700000D


Thank you for your help

2 ACCEPTED SOLUTIONS
watkinnc
Super User
Super User

Say you are at step name FullTable. Just filter to include only the accounts you want to duplicate, name this step Filtered, and then

 

= Table.Combine({FullTable, Filtered})

 

You can refer to any step in any order.

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

Kmcdonald
Helper III
Helper III

Hi Power_BI_new,

I put together your example provided to show you how I achieved this differently than the other suggestions.

 

So once I put together the data in an xlsx file, I started with the sample below. Please note that I had formatted the "Account Number" column as text.

 

Kmcdonald_0-1682209302046.png

 

So the first step was that I clicked the "Add Step" button to create a starting point and named the new step, "--original_data".

 

Kmcdonald_1-1682209770210.png

Kmcdonald_2-1682209806829.png

 

Then I used a filter on the "Account Number" column, using the text filter begins with a 6 or 7.

 

Kmcdonald_3-1682209998160.png

Kmcdonald_4-1682210029050.png

Then I appended the current query onto itself.

 

Kmcdonald_5-1682210095194.pngKmcdonald_6-1682210130350.png

 

Then as the last step, when you select the append query step it will show the name of the query twice. So I changed one of those names to the "--original_data" step name, that I name in the first step.

 

from:

Kmcdonald_9-1682210296090.png

 

to:

Kmcdonald_10-1682210340369.png

 

This is my final result, which I believe matches what you are trying to acheive.

 

Kmcdonald_11-1682210375226.png

 

If I'm not coming accross clearly, happy to share the pbix file I put together if you have somewhere I can drop it 🙂

If you need to, you can change the account number back to a whole number format.

 

 

View solution in original post

6 REPLIES 6
Kmcdonald
Helper III
Helper III

Hi Power_BI_new,

I put together your example provided to show you how I achieved this differently than the other suggestions.

 

So once I put together the data in an xlsx file, I started with the sample below. Please note that I had formatted the "Account Number" column as text.

 

Kmcdonald_0-1682209302046.png

 

So the first step was that I clicked the "Add Step" button to create a starting point and named the new step, "--original_data".

 

Kmcdonald_1-1682209770210.png

Kmcdonald_2-1682209806829.png

 

Then I used a filter on the "Account Number" column, using the text filter begins with a 6 or 7.

 

Kmcdonald_3-1682209998160.png

Kmcdonald_4-1682210029050.png

Then I appended the current query onto itself.

 

Kmcdonald_5-1682210095194.pngKmcdonald_6-1682210130350.png

 

Then as the last step, when you select the append query step it will show the name of the query twice. So I changed one of those names to the "--original_data" step name, that I name in the first step.

 

from:

Kmcdonald_9-1682210296090.png

 

to:

Kmcdonald_10-1682210340369.png

 

This is my final result, which I believe matches what you are trying to acheive.

 

Kmcdonald_11-1682210375226.png

 

If I'm not coming accross clearly, happy to share the pbix file I put together if you have somewhere I can drop it 🙂

If you need to, you can change the account number back to a whole number format.

 

 

Thank you very much for this detailed answer!

watkinnc
Super User
Super User

Say you are at step name FullTable. Just filter to include only the accounts you want to duplicate, name this step Filtered, and then

 

= Table.Combine({FullTable, Filtered})

 

You can refer to any step in any order.

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Thank you for your answer, but it gives me this error:

 

Sorry... We couldn't convert a value of type Table to type List:

Details :
Value = [ Table ]
Type = [ Type ]

Most likely you entered the code incorrectly, omitting the "{..}" around the List of tables

Works perfectly!!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors