How to add schema name to a table and append 25 more tables preserving query folding?

[MatheusLPS]

I have a SQL database with multiple schemas:

Goal: Apeend all "Data" table with Table.Combine and preserve query folding. It is working:

Problem: After the combine/append, I loose the schema name. So, I do not know where the data comes from.

What I am trying: Add the schema name to each of the "Data" tables and after that, Append all.

Table.AddColumn(Source, "NewTable", each Table.AddColumn([Data], "Schema", each [Schema]))

Result:

Error: The field 'Schema' from record was not found:

Question:

How do I add a new column dynamically to my "Data" tables with the name of the schema?

Is there another way? Please, I do not want to import each schema individually.

Thanks

[MatheusLPS · Accepted solution]

Hello. Just solved the problem. I was reading the forum to find another way.

Found this: https://community.powerbi.com/t5/Desktop/quot-Expression-Error-We-cannot-apply-field-access-to-the-t...

And this: https://ssbi-blog.de/blog/technical-topics-english/the-environment-concept-in-m-for-power-query-and-...

Solution: Store the Schema name in a variable by calling a function.

Table.AddColumn(Source, "NewTable", each Table.AddColumn([Data],"Schema", (X) => let Parameter = [Schema] in Parameter))

Table 1:

Table 2:

Table 32:

After Table.Combine all the xxxxx tables, query folding ON!!!! Finally!!!!!!

Complete code:

let
    Fonte = Sql.Database("Server", "Database", [CreateNavigationProperties=false]),
    FilterTables = Table.SelectRows(Fonte, each Text.EndsWith([Name], "NOTIFICATION_CONFIG")),
    AddNewColumn = Table.AddColumn(FilterTables, "NewTable", each Table.AddColumn([Data],"Schema", (X) => let Parameter = [Schema] in Parameter)),
    AppendAll = Table.Combine(AddNewColumn[NewTable])
in
    AppendAll