Forum Discussion

Marcin82's avatar
Marcin82
Frequent Visitor
4 years ago
Solved

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...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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"