Add custom column in multiple tables automatically

Anonymous · ‎10-17-2019

I have a power BI file with hundreds of tables. In a subset of those tables (all begin with "vw_card_") there is a common column named "spaid". I need to add a custom column to all of those tables that brings in the first 8 characters of the spaid column. Text.Start([spaId], 8). Is it possible to do this automatically or do I have to go into all ~100+ tables and add it manually?

artemus · ‎10-20-2019

I'm not sure how to mass modify a lot of queries, but if you want a combined table of those tables (possibly just with a shared column) you can use the following statement to get a record object of all queries in the current file. Note you can very easily hit firewall restrictions doing this.

#sections[Section1]

Nathaniel_C · ‎10-17-2019

@Anonymous ,

This may be a good quick question for @ImkeF who is the meister of M.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

ImkeF · ‎10-18-2019

From my experience there is a very good chance that these tables should be appended together as one before being loaded to the data model. Then you'd also only have to add a column to one table.

Add a column to each of the tables as with an identifier before appending.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries