Quick Post

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

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

let

Source = #"2018 FIFA World Cup Wiki (2)",

#"Removed Other Columns" = Table.SelectColumns(Source,{"Column2"}),

#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),

#"Promoted Headers" = Table.PromoteHeaders(#"Removed Duplicates", [PromoteAllScalars=true]),

#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 1, 1),

#"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},Query1,{"Index"},"Query1",JoinKind.LeftOuter),

#"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Custom"}, {"Custom"}),

#"Removed Other Columns1" = Table.SelectColumns(#"Expanded Query1",{"Custom", "Round of 16"}),

#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Round of 16", "R"}}),

#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Custom"}, {{"Count", each [R], type table}}),

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each [Count]),

#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom.1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)),

#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Custom", "Count"})

in

#"Removed Columns"

Referenced Query

let

Source = {"a".."h"},

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each {List.Repeat({[Column1]},3)}),

#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),

#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Custom"}),

#"Expanded Custom1" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),

#"Added Index" = Table.AddIndexColumn(#"Expanded Custom1", "Index", 1, 1)

in

#"Added Index"

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 🙂

List.Repeat() in #PowerQuery to duplicate rows