List.Repeat() in #PowerQuery to duplicate rows

Another quick, more of “Note to self” blog post. While I was working with FIFA Football Model, one of the queries has a dataset like below

The Challenge

But what I wanted was group every three rows into one group. So I can do further transformations to achieve a result like below.

So what have I done, I created a blank query to create my duplicate values. As I knew I needed eight groups, I created a base list in my blank query with following M

= {“a”..”h”}

Then I added a custom column with List.Repeat()

{List.Repeat({[Column1]},3)}

Then I added an index column which I used to join my base query, which gives me the following result after expanding.

Grouping values were easy with above state of data

Here is full M code:

Base Query

Power Query Solution

let
    // Source data from FIFA World Cup Wiki
    Source = #"2018 FIFA World Cup Wiki (2)",
    
    // Select and clean columns
    #"Removed Other Columns" = Table.SelectColumns(Source, {"Column2"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Duplicates", [PromoteAllScalars=true]),
    
    // Add index for joining
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 1, 1),
    
    // Merge with Query1 to get duplicate row groups
    #"Merged Queries" = Table.NestedJoin(
        #"Added Index",
        {"Index"},
        Query1,
        {"Index"},
        "Query1",
        JoinKind.LeftOuter
    ),
    
    // Expand the merged query
    #"Expanded Query1" = Table.ExpandTableColumn(
        #"Merged Queries",
        "Query1",
        {"Custom"},
        {"Custom"}
    ),
    
    // Select relevant columns
    #"Removed Other Columns1" = Table.SelectColumns(
        #"Expanded Query1",
        {"Custom", "Round of 16"}
    ),
    
    // Rename column for clarity
    #"Renamed Columns" = Table.RenameColumns(
        #"Removed Other Columns1",
        {{"Round of 16", "R"}}
    ),
    
    // Group rows by Custom column
    #"Grouped Rows" = Table.Group(
        #"Renamed Columns",
        {"Custom"},
        {{"Count", each [R], type table}}
    ),
    
    // Add custom column with grouped data
    #"Added Custom" = Table.AddColumn(
        #"Grouped Rows",
        "Custom.1",
        each [Count]
    ),
    
    // Extract and combine values
    #"Extracted Values" = Table.TransformColumns(
        #"Added Custom",
        {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ";"), type text}
    ),
    
    // Split combined values by delimiter
    #"Split Column by Delimiter" = Table.SplitColumn(
        #"Extracted Values",
        "Custom.1",
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)
    ),
    
    // Clean up final columns
    #"Removed Columns" = Table.RemoveColumns(
        #"Split Column by Delimiter",
        {"Custom", "Count"}
    )
in
    #"Removed Columns"

Referenced Query

let
    // Create base list from 'a' to 'h'
    Source = {"a".."h"},
    
    // Convert list to table
    #"Converted to Table" = Table.FromList(
        Source,
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
    ),
    
    // Add custom column with List.Repeat to duplicate each value 3 times
    #"Added Custom" = Table.AddColumn(
        #"Converted to Table",
        "Custom",
        each {List.Repeat({[Column1]}, 3)}
    ),
    
    // Expand the list column (first expansion)
    #"Expanded Custom" = Table.ExpandListColumn(
        #"Added Custom",
        "Custom"
    ),
    
    // Select only the Custom column
    #"Removed Other Columns" = Table.SelectColumns(
        #"Expanded Custom",
        {"Custom"}
    ),
    
    // Expand the list column again (second expansion)
    #"Expanded Custom1" = Table.ExpandListColumn(
        #"Removed Other Columns",
        "Custom"
    ),
    
    // Add index column for joining with base query
    #"Added Index" = Table.AddIndexColumn(
        #"Expanded Custom1",
        "Index",
        1,
        1
    )
in
    #"Added Index"

Key Takeaways

I genuinely feel like there must be an elegant way of grouping that data instead of creating another query, but then, this was the only way I could think off. Maybe with a bit of thinking, I could use List.Generate() to integrate and create duplicate values in the base query itself. Instead of using List.Repeat(), I can add a column by with list {1..3}, which also creates duplicate rows.  Like any other language, there are various ways but, I used List.Repeat() the very first time, hence this blog post. Hope it helps someone out there 🙂

Keep smiling, Prathy 🙂