Forum Discussion
Formatting tables in the same sheet independently
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
Super UserI 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 here
I cannot remove the the lines where first column says machine #, thats the point. I need to keep every single header row from the tables. In this case I have 2 tables in the same sheet in the same file and I need to unpivot both their headers independently.
- PwerQueryKees
That is how your screenshot of how the final result should be looks...
Yes, but that is after the transformation, I need to format every table and when they are all correct I put them together and delete the headers. Only AFTER the formatting.