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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors