Add a column to an SQL Selected Table

[YPOC]

Hello,

I'm loading a CSV file from a REST connection, now I want to add a column to it with a key so I can link the entries with other tables.

I'm loading the CSV like this:
for i = 1 to 5
let filename = i & '.csv';
RestConnectorMasterTable:
SQL SELECT
"MyCol1",
"MyCol2",
...
FROM CSV (header on, delimiter ",", quote """") "CSV_source"
WITH CONNECTION(Url "$(filename)");
NEXT i;

So I will get a big table with the entries of all CSVs right beneath each other. However I don't have a way to distinguish from which file they came. That's why I would like to add an extra column 'file' that stores the file name where each entry got loaded from.

If I understand correctly I can't use any calculated fields with an SQL Select statement directly. I thought of using
LOAD * INLINE [
// file
// '$(filename)'
// ](delimiter is ',');
right after the statement above, but this will straight up deliver no results. I think using Join or Keep are no options either, because I don't have a key to match yet.

What would be the best way to accomplish this?

[PrashantSangle · Accepted solution]

use preceding load to achieve that

like 

for i = 1 to 5
let filename = i & '.csv';
RestConnectorMasterTable:

Load *, filename() as filename;

SQL SELECT
"MyCol1",
"MyCol2",
...
FROM CSV (header on, delimiter ",", quote """") "CSV_source"
WITH CONNECTION(Url "$(filename)");
NEXT i;

Regards,

Prashant Sangle