Forum Discussion
Formatting tables in the same sheet independently
I have this file with multiple tables I want to format and I have the M code to format them. The problem is they have to be formatted individually. Is there a way in power query to separate them into sheets or something and apply the m code individually or maybe find out a way to format them without separating.
These are my tables:
The final result should be this:
The Part number for each table depends on the names of the columns in the original tables which vary a lot. This M code isnt for this exact file but for some files with similar structure and formatting just more columns:
filename = "INSERT FILE NAME",
#"Removed Blank Rows" = Table.SelectRows(Sheet1_Sheet, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),
//The following list should contain all of the columns which are to be removed
#"Removed Other Columns" = Table.RemoveColumns(#"Promoted Headers",{"Column1", "Tower", "Level", "Feeder Type", "Fixed Feeder", "Fixed Comp.", "Recipe Count","Recipe Commonality [%]","All Pickups"},MissingField.Ignore),
//Add the File Name column and then move it to the beginning
#"Added Column" = Table.AddColumn(#"Removed Other Columns","File Name", each filename),
//The following list should contain every column in the table except for the columns with the part numbers & values
#"Reordered Columns" = Table.ReorderColumns(#"Added Column",{"File Name", "Station", "Location", "Track", "Division", "Component"},MissingField.Ignore),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,"!NULLPLACEHOLDER!",Replacer.ReplaceValue,Table.ColumnNames(#"Reordered Columns")),
//The following list should contain every column in the table except for the columns with the part numbers & values
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"File Name","Station", "Location", "Track", "Division", "Component"}, "Part Number", "Value"),
#"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Other Columns","!NULLPLACEHOLDER!",null,Replacer.ReplaceValue,Table.ColumnNames(#"Unpivoted Other Columns"))
in
#"Replaced Value1"
I think I have an answer. Now im gonna test it in folders and not just individual files. I will leave my code so far here anyway.
Let
// Load the workbook and sheet
Source = Excel.Workbook(File.Contents("C:\Users\FEM6CLJ\Desktop\Excel\Teste panasonic.xlsx"), null, true),
Sheet = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
// Remove unnecessary columns
RemoveColumns = Table.RemoveColumns(Sheet, {"Column1","Column7", "Column8"}),
// Remove blank rows
RemoveBlankRows = Table.SelectRows(RemoveColumns, each List.NonNullCount(Record.FieldValues(_)) > 0),
// Find the positions where "Machine Name" appears to split the table
AddIndex = Table.AddIndexColumn(RemoveBlankRows, "Index", 0, 1, Int64.Type),
MachineNamePositions = Table.SelectRows(AddIndex, each [Column2] = "Machine Name")[Index],
// Function to split and process each sub-table
ProcessSubTables = (start, end) =>
let
SubTable = Table.Range(AddIndex, start, end - start),
RemoveIndex = Table.RemoveColumns(SubTable, {"Index"}),
PromoteHeaders = Table.PromoteHeaders(RemoveIndex),
AddFileName = Table.AddColumn(PromoteHeaders, "File Name", each "Teste panasonic"),
ReorderColumns = Table.ReorderColumns(AddFileName, {"File Name", "Machine Name", "Table", "Slot", "SubSlot", "Part"}, MissingField.Ignore),
PivotedTable = Table.UnpivotOtherColumns(ReorderColumns, {"File Name", "Machine Name", "Table", "Slot", "SubSlot", "Part"}, "Part Number", "Value")
in
PivotedTable,
// Split the main table into sub-tables based on the positions
TableRanges = List.Zip({MachineNamePositions, List.Skip(MachineNamePositions,1) & {Table.RowCount(AddIndex)}}),
ProcessedTables = List.Transform(TableRanges, each ProcessSubTables(_{0}, _{1})),
// Combine all processed tables
CombinedTable = Table.Combine(ProcessedTables)
in
CombinedTable
8 Replies
Should be doable, send actual test data (not a screenshot) and I will have a go...
I think I have an answer. Now im gonna test it in folders and not just individual files. I will leave my code so far here anyway.
Let
// Load the workbook and sheet
Source = Excel.Workbook(File.Contents("C:\Users\FEM6CLJ\Desktop\Excel\Teste panasonic.xlsx"), null, true),
Sheet = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
// Remove unnecessary columns
RemoveColumns = Table.RemoveColumns(Sheet, {"Column1","Column7", "Column8"}),
// Remove blank rows
RemoveBlankRows = Table.SelectRows(RemoveColumns, each List.NonNullCount(Record.FieldValues(_)) > 0),
// Find the positions where "Machine Name" appears to split the table
AddIndex = Table.AddIndexColumn(RemoveBlankRows, "Index", 0, 1, Int64.Type),
MachineNamePositions = Table.SelectRows(AddIndex, each [Column2] = "Machine Name")[Index],
// Function to split and process each sub-table
ProcessSubTables = (start, end) =>
let
SubTable = Table.Range(AddIndex, start, end - start),
RemoveIndex = Table.RemoveColumns(SubTable, {"Index"}),
PromoteHeaders = Table.PromoteHeaders(RemoveIndex),
AddFileName = Table.AddColumn(PromoteHeaders, "File Name", each "Teste panasonic"),
ReorderColumns = Table.ReorderColumns(AddFileName, {"File Name", "Machine Name", "Table", "Slot", "SubSlot", "Part"}, MissingField.Ignore),
PivotedTable = Table.UnpivotOtherColumns(ReorderColumns, {"File Name", "Machine Name", "Table", "Slot", "SubSlot", "Part"}, "Part Number", "Value")
in
PivotedTable,
// Split the main table into sub-tables based on the positions
TableRanges = List.Zip({MachineNamePositions, List.Skip(MachineNamePositions,1) & {Table.RowCount(AddIndex)}}),
ProcessedTables = List.Transform(TableRanges, each ProcessSubTables(_{0}, _{1})),
// Combine all processed tables
CombinedTable = Table.Combine(ProcessedTables)
in
CombinedTable
I think your are making it too complicated.
My appraoch would be:
- Remove blank lines
- Remove the lines where first columns is "Feeder Setup"
- Promote headers
- Remove all lines where first column says "Machine #"
And take it from there.
You now mention you have mutiple files (or sheets?)
- Get the Folder.Files
- Filter the files you need
- Do something like this (from Combining data sources with overlapping values - Microsoft Fabric Community)
let Source = Folder.Files("C:\Users\keess\OneDrive\Documents\- Tools en Programmeren\Power Query\Fabric Community\Combining data sources with overlapping values"), #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "System")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook([Content])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}), #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom.Kind] = "Sheet")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Name", "Custom.Data"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Name", "System"}}), #"Promoted Headers" = Table.TransformColumns(#"Renamed Columns",{{"Custom.Data", each Table.PromoteHeaders(_, [PromoteAllScalars=true])}}), #"Expanded Custom.Data" = Table.ExpandTableColumn(#"Promoted Headers", "Custom.Data", {"User", "Last Login"}, {"Custom.Data.User", "Custom.Data.Last Login"}), #"Pivoted Column" = Table.Pivot(#"Expanded Custom.Data", List.Distinct(#"Expanded Custom.Data"[System]), "System", "Custom.Data.Last Login", List.Max) in #"Pivoted Column"Call you logic for reformating a single file in hereThat is how your screenshot of how the final result should be looks...
If you start with someting like the steps up to an including
#"Renamed Columns"
You can then continue with the steps I outlined above.
Success! Feel free to post sample data and I will have a stab..
