Right click on “EnglishProductCategoryName” and select “Add as New Query”.In Query Editor window click “DimProductCategory” from Queries pane.Now we need to create a list from “DimProductCategory” table. Switch to “Relationships” view to make sure the relationships detected correctly then click “Edit Queries” from the ribbonĬreating a List Query from a Table Column:.Select “FactInternetSales”, “DimProduct”, “DimProductSubCategory” and “DimProductCategory” tables then click “Load”.Get data from SQL Server and connect to Adventure Works DW 2016 CTP3.But, now we can easily implement those sort of scenarios. But, what if we want to filter query results based on the values of a column from a particular table? Previously we couldn’t answer these sort of questions if we want to filter FactInternetSales based on a selected values of EnglishProductName column from DimProductCategories using Query Parameters.
#Excel 2016 query editor manage parameters how to
You also learnt how to use Query Parameters in Filter Rows.
#Excel 2016 query editor manage parameters series
In the first post of these series I explained how to create dynamic data sources using Query Parameters.
This feature is very useful and from now on we are not restricted to proviode a static list of values in “Manage Parameters”. Power BI development team added another cool feature to Power BI Desktop on July 2016 which is the ability to add a List Query output to a query parameter as it’s “Suggested Values” (formerly “Allowed Values”). Please remember to mark the replies as answers if they helped.In the previous posts, here and here, I explained how you can use Power BI Desktop Query Parameters for many different use cases.
Microsoft does not guarantee the accuracy of this information. Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. > It looks like we may need to venture into PowerQuery syntax to make this happen, but that seems rather complex for just passing in a date.īased on my research, I am afraid it might be necessary to use Power Query to pass parameters to SQL queries from Excel.Įarlier version of Microsoft Excel has a way of passing parameters to SQL queries via Data > Connections, but the parameters button is grayed out since Excel 2010 and upward.Īs for current versions of Excel, apart from the VBA method, most articles about passing parameters involve Power Query, like: Need to venture into PowerQuery syntax to make this happen, but that seems rather complex for just passing in a date. I've also seen methods that use VBA, but I need to train others on this process and VBA is a little beyond most users.Ĭan anyone provide me with the current process for adding a parameter to a query? Within the Query Editor I can create parameters, but there's no instructions on syntax for adding those parameters to the query. Most of the guides online say to use an "Other Data Source", which isn't an option in this version. Properties dialogue box which shows a Parameters button, however this is always grayed out. Simple right? From the query, going into Data> Connections (group) > Connections (button) > Properties brings up the Connection I have a MSSQL query loaded into a workbook and I'd like to pass date parameters from the workbook to the query. I feel like this answer should be obvious, but I've scoured Excel and the web without any success. Most of the how-to guides online are out-of-date and don't work in the version of Excel we're running (Office 365 Business Version 1611 build 7571.2109).