Multiple tables in worksheet equally transformed into multiple tables in another worksheet

So, first let me say that I haven't read your message in detail.

Now, you think well that parameterising and creating a custom function is a good option.

But, you could also do everything in one.

Assuming that:

1. All tables are formatted as tables.

2. Any table that you wish to transform this way, can be easily identified by its name (e.g. starts with "CD_")

3. You wish to view the result in one sheet

then you can have:

a) A function to format the tables as you wish (named TableToValues -referenced in next query):

(tbl as table) as table =>
let
    #"Removed Columns" = Table.RemoveColumns(tbl,{"Column1", "Column3", "Column4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column2", type text}, {"Column5", type text}, {"Column6", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column6]??0) <> 0),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Column2", "Column6", "Column5"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column2", "Type"}, {"Column6", "Amount"}, {"Column5", "Measure"}})
in
    #"Renamed Columns"

b) A single query to get all the tables formatted:

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "CD_")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Name", "Content"}),
    #"Transformed Tables" = Table.TransformColumns(#"Reordered Columns",{{"Content", TableToValues, type table}}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Transformed Tables", "Content", {"Type", "Amount", "Measure"}, {"Type", "Amount", "Measure"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Content",{{"Type", type text}, {"Amount", Int64.Type}, {"Measure", type text}})
in
    #"Changed Type"

Those two could use some optimising, but I think they are good enough.

Your result will look like this:

So, if you want it a bit better formatted, I would suggest using a pivot table afterwards as they tend to look better visually in such cases.

Cheers,