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