Forum Discussion
reuse query steps
Very new to Power BI, so please forgive me if this is something simple I'm missing.
Each week I get a spreadsheet sent to me that I want to add into Power BI as a new table/data source. The spreadsheet arrives in the same poor state every week and needs the same cleanup steps each time (remove blanks, delete columns etc. etc.) Is there any way I can use the cleanup steps I created in my first query for my first import on these additional tables? In essence I'm trying to use the cleanup steps like a macro in Excel I guess.
Thanks in advance for any help :smileyvery-happy:
Dragon1 If spreadsheet is going to have same number of columns and names just with poor data each time then in power bi desktop you can go to query editor, right click your original query and make duplicate of it. Then simply click on setting icon next to Source (first step in query) and point it to your new spreadsheet and click Refresh. This will repeat all those steps you applied previously. As said earlier if metadata of your spreadsheet hasn't changed but only data needs cleaning.
9 Replies
Dragon1 If spreadsheet is going to have same number of columns and names just with poor data each time then in power bi desktop you can go to query editor, right click your original query and make duplicate of it. Then simply click on setting icon next to Source (first step in query) and point it to your new spreadsheet and click Refresh. This will repeat all those steps you applied previously. As said earlier if metadata of your spreadsheet hasn't changed but only data needs cleaning.
Is there a way that this repeated steps can run automatically? For example, the new file comes into a folder every week and run a schedule job to pick up the file and run the "applied steps", etc... Thanks!
Thank you so much ankipatira. This works perfectly for what I need :smileyvery-happy:
ankitpatira Is it possible to do this if I want to apply the steps to a different sheet in the same workbook? When I go to change the source it only allows me to change the primary file and does not redirect me to choose the sheet like it does when I first Get Data. Thank you.
In the edit queries mode,
go to the advanced editor of the table where you have done the changes and copy the steps that involves the changes.
Go to the advanced editor of the table to which you want to replicate the changes and append the codes that you copied.
The changes will be effected.
Ensure the references are correct and there are no errors after you paste the format/cleanup codes.
Hi guys,
I know, I am very late to this party, but I'd like to offer another approach on this topic. You could outsource your M code transformations into functions and then use those to run against any target table. I happened to have written an article about this, 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 very 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 helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
