Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Marcin82
Frequent Visitor

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

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. Untitled.png

 

 

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

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"

vkkfmsft_0-1654760738747.png


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.

View solution in original post

8 REPLIES 8
v-kkf-msft
Community Support
Community Support

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"

vkkfmsft_0-1654760738747.png


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
Frequent Visitor

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
Super User
Super User

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

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

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
Super User
Super User

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors