Sometimes in Power Query, you’ll come across the need to duplicate rows as a part solution to a larger problem. In this blog post, I’ll share 2 examples of duplicating rows in Power Query.
Duplicate Rows in Power Query – Video
Duplicate Rows – Example 1
Consider the table below. I would like to duplicate this data twice for year 2019 and 2020.
Let’s start by creating a Custom Column (named year) containing a list. Here is the M Code.
= {2019,2020}
The new Year Column now shows a list with 2 values 2019 and 2020.
And on expanding the Year Column, we get entire data duplicated twice, once for each year.
Duplicate Rows – Example 2
In this particular example, I don’t intent to duplicate rows by a fixed number, rather the duplication will be based on the Freq Column. For eg Customer C will have 3 rows since the Freq value is 3.
I will create a blank list (by using{“”}) and repeat it by the reference of the Frequency Column. See the following M-Code I used to create a new Custom Column.
= List.Repeat({''},[Freq])
If I preview the output for Customer C, I can see that although the list is blank but it has 3 rows. And on expanding the custom column, we’ll duplicate rows based on the Frequency column.
In the end you should delete the blank Custom Column created. The final output seems like this and Customer C has now 3 rows.
More on Power Query
Generate Dates in Rows between Start and End Date – Power Query Challenge
Automate repetitive data cleaning tasks using Power Query
A comprehensive course to learn Power Query to automate all your mundane and repetitive data cleaning tasks in Excel or in Power BI
DOWNLOAD THE COURSE OUTLINE | ENROLL IN THE COURSE
