Duplicate Rows in Power Query

Duplicating Rows in Power Query

The video demonstrates two ways to duplicate rows in Power Query: repeat every row a fixed number of times, or repeat each row according to a value in a column.

Repeat Every Row a Fixed Number of Times

To create a copy of each row for two years, such as 2019 and 2020:

1. Go to Add Column → Custom Column.

2. Create a list containing the values to associate with each repeated row: powerquery {2019, 2020}

3. Expand the list column to new rows.

Each original record then appears once for each list item, producing two rows per record.

Repeat Rows According to a Frequency Column

To repeat each record a different number of times based on its frequency:

1. Go to Add Column → Custom Column.

2. Use List.Repeat with a placeholder list containing one blank value and the row’s frequency as the repetition count. For example: powerquery List.Repeat({""}, [Frequency])

3. Expand the resulting list to new rows.

A frequency of 1 produces one row, while a frequency of 3 produces three rows.

The central technique in both examples is to create a list for each record and expand that list into rows.

Speaker and Source

• Presenter: A presenter from Goodly; the name is not clearly stated in the subtitles.