Create a Dynamic File Path - Change the Power Query Source Based on a Cell Value

let's say you wanted to create a dynamic file path to what your query is connecting and all you want to do is update the file path in excel and hit refresh and your query updates with the new file's data here's how to do this [Music] we have two folders one for january and one for february in the january folder is the workbook called sales underscore data which shows the sales rep name sales order number sales value status product and month and the february folder also has a workbook called sales underscore data with the same data structure it's important to note that while the folder names can be different in this case it's january and february the name of the excel workbook needs to be the same so in this case the name is sales underscore data let's go into the january sales data workbook and this data is in a table format and the table is called sales data and if we go into the february sales data workbook the data is also in a table format and the table is called sales data as well so the table names also need to be the same to ensure there are no errors in your dynamic file path now to create your query open up a blank workbook go to data get data from file from workbook i've copied the file path for my january sales data so let's paste that here and click on open and double click on our sales data workbook this step is quite important we need to select the sales data table as remember this is the common table name that is in our february file also then click on transform data as we want to do some quick transformations to generate our monthly reports for the first report we want the top sales product for the month on the left here in the queries pane right click on sales data and click on reference as we want our report to reference our main source data so if there are any changes that are made in our source data it will reference it back to this query we've just created let's rename this to top sales product if you don't want to go through the transformations please check out the timestamps in the description to skip ahead next click on the cells value column and hold down the control key and select the product column right click and select remove other columns next let's click on group by and we want to group by product let's name the new column top sales product and we want it to be summed up by sales value and click on ok and we have total sales value by product let's click on the drop down next to top sales product and sort descending so we have our first report to create our second monthly report let's right click on the sales data query again and select reference let's rename this to commission calculation let's select the sales rep column hold down the control key and select the sales value column and the status column right click and select remove other columns we want to calculate a 10 commission on the sales value if the status is closed so let's quickly do that in the add column tab click on custom column let's name the column name commission calculation and we're going to use the if statement to calculate our commission type equals if then select status on the right year equals closed in double quotation marks type then and select the sales value column on the right times 0.1 else 0. our formula says if status is closed then multiply sales value by 10 percent else return 0 no syntax errors have been detected let's click on ok and we have our commission calculation column let's select the sales rep column and hold down the control key and select sales value and commission calculation right click and select remove other columns so we have our two monthly reports let's send these reports back to excel click on file close and load and our reports are all added in these different tabs let's move the top sales product report to the sales data tab and do the same for the commission calculation tab let's delete these two tabs as they're blank now click on the commission calculation tab and hold down the shift key and click on the top sales product tab right click and select delete and click on delete let's select both our tables and click on table design and let's select this table style here now to create the dynamic file path i've inserted a few lines at the top here and added our file parts for january and february you can place your file path anywhere you like even in a new tab if you prefer let's move the january file path to the cell under the file path cell we're doing this so that we can create a table so select the two cells and press ctrl t to convert the data to a table and select my table as headers and click on ok in the table styles let's remove the table format and uncheck filter button as i don't want this to look like a table let's name this table excel file path and send this to power query click on data fram table or range and here in our queries pane on the left we have our excel file path imported as a table what we actually need is for this table to be an object so that we can insert it into our sales data query let's remove the change type step as we don't need that to create that object right click on the side of the january file path here and click on drill down and the value for the january file path is returned as an object as we can see from this icon on the left here let's go back to our sales data query and click on source and here in the formula bar we have the excel.workbook function that brings in our source data using this file path here we need to figure out which portion of this file path we need to remove and insert our excel file path object that we just created our excel file path object starts with c for c drive and ends with january there is no backslash so that means we can remove everything from the first quotation mark until the word january and type excel and the intellisense brings up our object select excel file path then enter a space and type the ampersand sign and insert the quotation marks again as everything in quotation marks is fixed and it points to the excel workbook cells underscore data then hit enter and we get this formula firewall error it says this query references other queries or steps so it may not directly access a data source please rebuild this data combination power query is not allowed to access two different data sources originating from different queries in the same step there are two ways to fix this the one is to ignore this error and the other is to rebuild the data combination i will show you how to ignore the error for this workbook if you would like me to show you how to rebuild the data combination please let me know in the comments below to fix this error click on file options and settings and click on query options under current workbook click on privacy and here in privacy levels combine data according to your privacy level settings for each source is selected as the default this option controls how queries that combine data from multiple data sources behave let's instead click on ignore the privacy levels and potentially improve performance which means that data privacy settings are completely ignored when queries combine data from multiple data sources and if you hover over the information icon here it shows that the setting could expose sensitive or confidential data to an unauthorized person so ignoring data privacy checks makes it more likely that you or one of your users could create a query that accidentally sends data to an external data source which could breach your organization's rules so please comment below and let me know if you'd like me to show you how to rebuild the data combination next click on ok let's click on refresh preview and all our queries are fixed as they all reference the sales data query now let's click on our excel file path object to select it and send it back to excel as a connection only close and load to and select only create connection and click on ok so now if we move our january file path out of this table and bring in our february file path and hit refresh all our query automatically updates with our february sales data creating dynamic queries can save you hours in your busy day that's why i highly recommend you watch this video here on how to automate your vlookup and make it dynamic thank you for watching and see you in the next video you

Channel: Miss Microsoft

Views: 29,379

Keywords: excel, microsoft excel, power query, power query with input variables from excel sheet, parameter table, parameter query, extract records from data set, how do i get criteria from excel sheet into power query?, power query variable input from excel, excel power query, get & transform, power query advanced, power query parameters from cell, power query parameters power bi, power query parameters excel, business intelligence, power bi

Id: T5aWvqf9SnM

Length: 10min 20sec (620 seconds)

Published: Sat Apr 02 2022