Power Query custom function to loop over multiple tables and do the required transformation

[rashidanwar]

Hi Everyone,

I have multiple tables (Around 10) in Power Query and I want to do some transformation to all the Tables. Say I want to add a custom column to all the tables. 

I want a custom function which when invoked could loop over each table one by one and add the custom column to each table.

Is that even possible in Power Query?

Thanks in advance for any help in this regard.

Rashid

[rashidanwar]

Thank you @lbendlin for all the information. 

[rashidanwar]

Got it @lbendlin . If I follow your approach, then I will have a list of tranformed tables or a  table containing 3 rows, each row referencing to a tranformed table. Can I extract each table as seprate table?

[lbendlin]

Not in Power BI. Maybe in Excel. Maybe via the Graph API 

[rashidanwar]

@lbendlin, thank you for the response! 
Yes, I know that we can iterate over rows of table and each row could refer to a table, but my scenario is little different.

I have a key column in all the tables which I want to convert to a primary key column using Add.Key() funtcion.

So I have 
Table1 
Table2
Table3
in Power Query.

I want to create a custom function, that could iterate over each individual table and perform the Table.AddKey(table, "Key", true). In other word this would be added a last tranformation step in each tables' APPLIED STEPS pane.

You can suggest any other better approach. Please note that I have separate tables and after the above transformation I still want them seprate.

Thank you!

[lbendlin · Accepted solution]

Yeah, no.  You cannot inject transforms into queries from the outside. You need to do these transforms in each of the queries, or follow my approach and then use references.

[lbendlin]

yes, that is possible, and it is standard procedure in Power Query, Actions usually iterate over the rows of a table (which can reference your tables).

What do you want to do after you modified the tables?