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

[Marcin82]

Hello, 
My question is how to split a table in Power Query "vertically". The orignal table contains repating columns (headers). So it should be splited every 4rd columns. Then all of splitted tables should be appended so the final one has good structure. 

[v-kkf-msft · Accepted solution]

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.

[Marcin82]

Sorry, this is my first post. The sample data is here 

https://docs.google.com/spreadsheets/d/1dq-bGiQw1-Dc7j4rwnDRCl5pns0sEgFG/edit?usp=sharing&ouid=10256...

[AlexisOlson]

If you can create a column header list, then you can reshape the data with some pivoting and unpivoting.

Headers list:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Kept First Rows" = Table.FirstN(Source,2),
    #"Filled Down1" = Table.FillDown(#"Kept First Rows",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25"}),
    #"Transposed Table" = Table.Transpose(#"Filled Down1"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",each [Column1], each if Text.Contains([Column1], "LP") or Text.Contains([Column1], "marks") then null else [Column1],Replacer.ReplaceValue,{"Column1"}),
    #"Filled Up" = Table.FillUp(#"Replaced Value",{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Filled Up","KTO ŁADUJE",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down2" = Table.FillDown(#"Replaced Value1",{"Column1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Filled Down2","remarks","Remarks",Replacer.ReplaceText,{"Column2"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value2",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged")[Merged]
in
    #"Merged Columns"

Use this list in the following query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source), Headers})),
    #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{Headers{0}, "LP"}}),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns1",2),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows", List.Skip(List.Select(Headers, each Text.Contains(_, "LP")))),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"LP"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Date", "Column"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Column]), "Column", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column","Tusday","Tuesday",Replacer.ReplaceText,{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"LP", Int64.Type}, {"Date", type date}, {"A", type text}, {"B", type text}, {"Remarks", type text}}, "en-IN"),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}, {"LP", Order.Ascending}})
in
    #"Sorted Rows"

[watkinnc]

You could use Table.FromColumns(List.Zip(List.Split(Table.ToColumns(TableName), 4)))

--Nate

[Anonymous]

Hey @Marcin82 

The only way to do what u requested is to duplicate the main query as many as the total division you want... 

Remove the columns you dont want within each query and then, select "Home>Append Query as New" and select all recent created queries in order to append and create the wanted table. 

[edhans]

Try this:

1. Set the current table to not load. Right-click, and ensure Enable Load is not checked.
2. Create 3 References to the original query. Right-click, Reference. Name each one accordingly and set them to also not load.
3. In the first refrenced query, remove all but the first 4 columns.
4. In the second referenced query, keep only the middle 4 columns
5. In the third, keep only the last 4 columns.
6. Go back to the first referenced query, and in the Home ribbon, click the dropdown next to Append and select Append as New.
7. In that dialog box, set it to allow multiple tables
8. Ensure referenced queries 1, 2, and 3 are listed.
9. Rename this Append query as your main table. Make sure it is set to load.

[Marcin82]

Thank You for Your answer but I have already knew this solution and it is not perfect.  I'd like to have some more dynamic solution.
The problem is that that original table has 26 "blocks" of 4 columns. And it may change so a perfect solution will split the table every 4th column or something like that.

[edhans]

Ok, you didn't say that. Give us some data to work with. I cannot play with Power Query with an image. 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.