Power Query create column in embedded table with value from outside row

[colinhahn]

I am trying to load a list of files from a folder and combine them via Power Query. I am running into problems because some of headers in each file are different (they represent metadata fields from the database export, and fields have been added or deleted over time).

My end goal is a table with the following structure:

Report Date	Header1	Header2	Header3
1/1/2021	ABC	null	DEF
1/1/2021	GHI	null	JKL
2/1/2021	MNO	PQR	null
2/1/2021	STU	VWX	null

In other words, the file that was generated on 1/1 has columns for Header1 and Header3, but not Header2. The report from 2/1 has Header1 and Header2, but not Header3.

I can get my Power Query to the following state (let's call this #Almost):

Report Date	Contents
1/1/2021	Table (which contains columns for Header1 and Header3, with those values as actual headers rather than the first row of data)
2/1/2021	Table (which contains columns for Header1 and Header2, in the same format as above)

If I do a Table.Combine(#Almost[Contents]), I get the final table I want except the report date is lost.

What is the missing step I need to do? I assume there's a way with Table.TransformColumns, but I can't figure out how to "see" the value of [Report Date] from within the TransformColumns command.

 

[JGordon11]

let
    TAC =Table.AddColumn, TFR = Table.FromRows, d = #date,
    tcn = {"Report Date","Header1","Header2","Header3"},
    tbl1 = TFR({{"ABC","DEF"},{"GHI","JKL"}},{"Header1","Header3"}), 
    tbl2 = TFR({{"MNO","PQR"},{"STU","VWX"}},{"Header1","Header2"}),
    Almost = Table.FromColumns({{d(2021,01,01), d(2021,02,01)},{tbl1,tbl2}},{tcn{0},"Content"}),
    tblToCombine = Table.SelectColumns(TAC(Almost, "Tables", each TAC(_[Content], tcn{0}, (x)=> Record.Field(_, tcn{0}))),"Tables"),
    Result = Table.ReorderColumns(Table.Combine(tblToCombine[Tables]),tcn)
in
    Result

 

[colinhahn]

let
    TAC =Table.AddColumn, TFR = Table.FromRows, d = #date,
    tcn = {"Report Date","Header1","Header2","Header3"},
    tbl1 = TFR({{"ABC","DEF"},{"GHI","JKL"}},{"Header1","Header3"}),
    tbl2 = TFR({{"MNO","PQR"},{"STU","VWX"}},{"Header1","Header2"}),
    Almost = Table.FromColumns({{d(2021,01,01), d(2021,02,01)},{tbl1,tbl2}},{tcn{0},"Content"}),
    tblToCombine = Table.SelectColumns(TAC(Almost, "Tables", each TAC(_[Content], tcn{0}, (x)=> Record.Field(_, tcn{0}))),"Tables"),
    Result = Table.ReorderColumns(Table.Combine(tblToCombine[Tables]),tcn)
in
    Result

Can you explain more what is happening in the tblToCombine line? My actual report has a lot more data, and I'm struggling to figure out how to adapt the parts with tcn{0}.

 

[JGordon11]

That line does three things:

1) Adds a column called "Report Date" to each table in [Content] column populating with the date in [Report Date]
I defined tcn in line 2 to be a list of the column headers so tcn{0} is the first element in that list which is "Report Date"
Record.Fields(_,tcn{0}) is the same as Record.Field(_,"Report Date") which is also the same as _[Report Date]
here the _ represents each record in the table that the Table.AddColumn function steps though
2) Adds a column called "Tables" to the table containing the revised tables in [Content] from 1) above
3) Selects only that "Tables" added column in the table to be used in the next Combine and reorder step

It should work just as well without the third part, i.e., Table.SelectColumns