Formatting tables in the same sheet independently

[Anonymous]

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"

[PwerQueryKees]

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

[PwerQueryKees]

I think your are making it too complicated.

My appraoch would be:

1. Remove blank lines
2. Remove the lines where first columns is "Feeder Setup"
3. Promote headers
4. Remove all lines where first column says "Machine #"

   And take it from there.

   You now mention you have mutiple files (or sheets?)

   1. Get the Folder.Files
   2. Filter the files you need
   3. 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

[Anonymous]

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

[Anonymous]

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.

[PwerQueryKees]

OK. Then I totally do not understand what you are trying to achieve here....

[PwerQueryKees]

Should be doable, send actual test data (not a screenshot) and I will have a go...

[Anonymous · Accepted solution]

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