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

colinhahn

New Member
Joined
Jan 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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 DateHeader1Header2Header3
1/1/2021ABCnullDEF
1/1/2021GHInullJKL
2/1/2021MNOPQRnull
2/1/2021STUVWXnull

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 DateContents
1/1/2021Table (which contains columns for Header1 and Header3, with those values as actual headers rather than the first row of data)
2/1/2021Table (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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
285
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Power Query:
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

New Member
Joined
Jan 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Power Query:
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

Active Member
Joined
Jan 18, 2021
Messages
285
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,482
Messages
5,741,409
Members
423,658
Latest member
Kumaradas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top