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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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