let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 11), type number),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-GB")[Modulo]), "Modulo", "Column1"),
#"Filled Up" = Table.FillUp(#"Pivoted Column",{"1", "2", "3", "4", "5", "6", "7", "8", "9", "10"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([0] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"company", type text}, {"sector", type text}, {"industry", type text}, {"continent", type text}, {"country", type text}, {"marketvalue", type number}, {"sales", type number}, {"profits", type number}, {"assets", type number}, {"rank", Int64.Type}, {"forbeswebpage", type text}})
in
#"Changed Type1"
At the MODULO stage, enter 11 instead of 5.
Bookmarks