Dynamically expanding tables within tables for each column

[TomJefferies]

Hello,

Im trying to write a set of queries that will help me consolidate a lot of data from many reports - there are 3 phases to look at and the phases have differing amount of data points i need to look at:
Phase 1 has 4 data tags, Phases 2 and 3 have 2 tags each.

For each tag each file has roughly 48K lines so processing the data into the format required has required a couple of longwinded steps but im happy with that step. The issue im having is as follows:
At the end of each individual file being processed the output looks something like this:

And then once all files have been consolidated for one phase the table layout now looks like this (note im using a subset of four files to develop the solution hence com1-4) but as more files are added i would see an additional column per:

So for one finalised table i would need to expand out a specific column from the first screenshot, for example (FIT_Y01...) into each ComX column, and then from that expand the "Value" column to show the actual numerical values from the raw files.

The function i am writting for this is a nested table expand but i figure out the syntax i need to get the output im after.
My current function is this:

= (Tbl as table, tagFilter as text) as table => let
        cols = Table.ColumnNames(Tbl),
        TransformDef = List.Transform(cols, each {_,Table.ExpandTableColumn(Table.ExpandTableColumn(Tbl,_,{"Value"},_), _,{tagFilter}),{_}}),
        TransformCols = Table.TransformColumns(Tbl, TransformDef)
    in
        TransformCols

I would also like to keep the original column headers from the second table i showed as the column headers at the end of the function.
If it helps the M Code to expand one column looks like this on the first expansion:

= Table.ExpandTableColumn(Source, "COM1", {"FIT_Y01_03.PV"}, {"COM1"})

and then the second expansion looks like this:

= Table.ExpandTableColumn(#"Expanded COM1", "COM1", {"Value"}, {"COM1"})

Sorry for the long post ive been stuck on this for a few days and my brain is fried.

Thanks for any input and if i missed anything let me know!

[lbendlin]

Sounds like a lot of hard coding.  Have you considered unpivoting your meta tables instead and then applying the same transform to all rows?