Apply same transformations to multiple tables/excel sheets

[Syndicate_Admin]

Good afternoon

I'm looking for a way for the transformations I apply to modify a set of tables or Excel sheets.

Something similar to when we connect to a folder, select "combine and transform" and obtain the function automatically with the example file.

[tackytechtom]

Hi guys,

I know, I am very late to this party, but I really like lbendlin's approach here: Outsourcing your M code transformations into functions and then reusing those to run against your target tables.

I happened to have written an article about this topic, where you first define your functions and then populate a configuration table specifying where you wanna apply those functions to. Finally a Data Quality Check / Cleaning function leverages the previous information to do the cleansing. It's a decently scalable approach since the logic is decoupled and can be reused easily as many times as you want. The blog article can be found here.

Hope this can help someone passing by this thread in the future 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Syndicate_Admin]

Thank you for the reply.

I manage to do it through a parameter that focuses on a list generated with unique values of a column of a table, changing the default value that forces you to enter the parameter. But how to make the parameter have that list as a default value and not a single value of it? Or do you have put it another way to generate all the tables with the same transformation at once?

The data source function = folder applying merge and transform data, generates a sample file (which in our case would be a value of the list (list extracted from the column values of a table).
How is it solved with M code?
Thank you very much in advance to any help.

[lbendlin · Accepted solution]

That's what I meant when I wrote: "Apply the transforms against one of the sheets or files, then extract the code into a function, then apply that function to all sheets or files".

A parameter can be fed from a list query.  But that's rather pointless as you can instead use that query directly in your ETL.

[lbendlin]

Apply the transforms against one of the sheets or files, then extract the code into a function, then apply that function to all sheets or files.  Similar to what you would do in Excel where you record a macro for your steps and then examine the resulting macro to include in your code.