Power QUERY Conditional Date Column Header

[Innesmacleod]

Good Morning,

I am trying to combine two different files with similar columns; each will have multiple files for each workday in the month (one called inflows and one called outflows, times no. of working days in month). 18.05.2023 inflows 18.05.2023 outflows
They both have a common issue in that there is no date in column in the data however there is a date at the top of the spreadsheet (example 1).

I am having an issue with adding a date column to a file that will be combined with other files in a folder with different dates.
My original file has data in a table however it doesn't contain a date column, the date for the file is shown above the data in example 1.
I have tried adding a conditional column in the sample file that takes the date and fills the data down (Example 2).

However when I go to remove the top rows, and then make headers (Data1, Data 2 etc), I then go to add another file to the folder it is looking for the column header called 18/05/2023 and so I get an error loading the other file as there is no column called 18/05/2023.

I have tried adding a new header called date but I still get an error because it is looking for header called 18/05/2023 in each of the other files and it is not found .
Would anyone be able to help as I try to get my table to look like Example 4?

One other point to add about the sample file; because the inflow and outflow files have different number of headers and slightly different header names, I am using a mapping to map the columns from Inflow to the outflow headers using the following code:

"= Table.TransformColumnNames(#"Promoted Headers", each

List.Accumulate(Table.ToRecords(#"Mapping Table"), _ ,

(state, current)=> Text.Replace(Text.Upper(state),current[BEFORE],current[AFTER]) ))"

(

Hope that explains my problem

Thanks,

Innes

Example:1

Example: 2

Example 3

Example 4

[Innesmacleod · Accepted solution]

I've found an answer on the above here; thanks @vijayAVerma

Solved: Referencing a column by position not name as name ... - Microsoft Power BI Community