How to split a table into multiple table based on every x column

Hi Marcin82 ,

It may not be the optimal solution, but the following code will automatically separate every 5 columns regardless of how many columns you have.

let
    Source = Excel.Workbook(File.Contents("\\xxxxxx\TESTdited 2.xlsx"), null, true),
    #"STYCZEŃ 2022_Sheet" = Source{[Item="STYCZEŃ 2022",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"STYCZEŃ 2022_Sheet",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type any}, {"Column12", type any}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type any}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type any}, {"Column22", type any}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}}),

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","","""null""",Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type")),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"""null""",Replacer.ReplaceValue,Table.ColumnNames(#"Replaced Value")),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each Number.IntegerDivide( Number.FromText( Text.Select([Attribute],{"0".."9"}) ) ,5.0001)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom", "NewColumnName", each "Column" & Text.From(Number.FromText( Text.Select([Attribute],{"0".."9"})) - [Custom]*5)),
    #"Grouped Rows" = Table.Group(#"Added Custom3", {"Custom"}, {{"Count", each _, type table [Attribute=text, Value=any, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Group(Table.TransformColumnTypes([Count],{{"Value", type text}}),"NewColumnName", {"Tab", each List.Skip([Value],1)}
)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each let net = [Custom.1]
in
Table.Pivot( net, List.Distinct(net[NewColumnName]), "NewColumnName", "Tab")),
    #"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Custom2", "Custom.2", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Added Custom4" = Table.AddColumn(#"Expanded Custom.2", "AllList", each List.Zip({[Column1],[Column2],[Column3],[Column4],[Column5]})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom4",{"AllList"}),
    #"Expanded AllList" = Table.ExpandListColumn(#"Removed Other Columns", "AllList"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded AllList", {"AllList", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "AllList", Splitter.SplitTextByDelimiter(";", QuoteStyle.None), {"AllList.1", "AllList.2", "AllList.3", "AllList.4", "AllList.5"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Split Column by Delimiter","""null""","",Replacer.ReplaceText, Table.ColumnNames(#"Split Column by Delimiter")),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value2",{{"AllList.1", Int64.Type}, {"AllList.2", type text}, {"AllList.3", type text}, {"AllList.4", type text}, {"AllList.5", type text}})
in
    #"Changed Type2"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.