cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dragon1 Frequent Visitor
Frequent Visitor

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 Smiley Very Happy

1 ACCEPTED SOLUTION

Accepted Solutions
ankitpatira Super Contributor
Super Contributor

Re: reuse query steps

@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.

5 REPLIES 5
ankitpatira Super Contributor
Super Contributor

Re: reuse query steps

@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.

Dragon1 Frequent Visitor
Frequent Visitor

Re: reuse query steps

Thank you so much ankipatira. This works perfectly for what I need Smiley Very Happy

vcheng1 Occasional Visitor
Occasional Visitor

Re: reuse query steps

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! 

tc5pt Frequent Visitor
Frequent Visitor

Re: reuse query steps

@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.

Highlighted
jithu0 Visitor
Visitor

Re: reuse query steps

@tc5pt 

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.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 121 members 1,684 guests