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

Assistance with SQL Parameters in Power BI - Help with creating a list from SQL Query

So I have been trying to follow this link to create a list in Power BI. For a little background, I am attempting to create a dataset within Power BI that has products, their list price, quantities sold, etc. But I would like to filter this by category and eventually by subcategory using AdventureWorks2014 Database. It was incredibly difficult to figure out a way to do this with step by step guides. (Are there any clear resources that discuss this?...I came across some questions on this forums and the answers pointed to a blog that wasn't of much help). I finally came across this link that provided me a little clarity https://www.mssqltips.com/sqlservertip/4475/using-parameters-in-power-bi/. I have to create a list, I can't simply simply do a normal SQL query like the one I was sttempting to do, because when I select for Query when creating a parameter, the list does not show up. Below is my code, where AventureWorks2014 is the database, dbo is the schema, Production.ProductCategory is the table and "Name" is the field I want to create a list from.

 

let
    Source = Sql.Database("localhost", "AdventureWorks2014"), 
    dbo.Production.ProductCategory = Source{[Schema = "dbo", Item = "Production.ProductCategory"]}[Data],
    #"Added Custom" = Table.AddColumn(dbo.Production.ProductCategory, "Name"),
    CategoriesList = #"Added Custom"[ProductCategoryID],
    #"RemovedDuplicates" = List.Distinct(CategoriesList)
in
    #"RemovedDuplicates"

I get the following error:

 

Expression.Error: The key didn't match any rows in the table.
Details:
    Key=Record
    Table=Table

 

What am I doing wrong? And is there a way to create a key, value pair list? Where I could have the categoryID matched with the categoryName? Although I don't think it would be necessary for my purposes. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ck18 Frequent Visitor
Frequent Visitor

Re: Assistance with SQL Parameters in Power BI - Help with creating a list from SQL Query

 

@v-cherch-msft, The following worked for me. I was getting my tables and schema mixed up. Also, I needed to use the "each" function to populate the column.

 

let
    Source = Sql.Database("localhost", "AdventureWorks2014"),
    Production_ProductCategory = Source{[Schema="Production",Item="ProductCategory"]}[Data],
    #"Added Custom" = Table.AddColumn(Production_ProductCategory, "CategoryName", each ProductCategory.Name),
    CategoriesList = #"Added Custom"[Name],
    #"RemovedDuplicates" = List.Distinct(CategoriesList)
in
    RemovedDuplicates
5 REPLIES 5
Community Support Team
Community Support Team

Re: Assistance with SQL Parameters in Power BI - Help with creating a list from SQL Query

Hi @ck18

 

Here is the document for you:

https://www.excelguru.ca/blog/2016/04/28/pass-parameters-to-sql-queries/

 

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ck18 Frequent Visitor
Frequent Visitor

Re: Assistance with SQL Parameters in Power BI - Help with creating a list from SQL Query

@v-cherch-msft thank you. I finally figured out how to create the list but this documents seems to have other useful information as well.
Community Support Team
Community Support Team

Re: Assistance with SQL Parameters in Power BI - Help with creating a list from SQL Query

Hi @ck18

 

Would you share your solution and mark it as solution? That way, other community members will easily find the solution when they get same issue.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ck18 Frequent Visitor
Frequent Visitor

Re: Assistance with SQL Parameters in Power BI - Help with creating a list from SQL Query

 

@v-cherch-msft, The following worked for me. I was getting my tables and schema mixed up. Also, I needed to use the "each" function to populate the column.

 

let
    Source = Sql.Database("localhost", "AdventureWorks2014"),
    Production_ProductCategory = Source{[Schema="Production",Item="ProductCategory"]}[Data],
    #"Added Custom" = Table.AddColumn(Production_ProductCategory, "CategoryName", each ProductCategory.Name),
    CategoriesList = #"Added Custom"[Name],
    #"RemovedDuplicates" = List.Distinct(CategoriesList)
in
    RemovedDuplicates
Community Support Team
Community Support Team

Re: Assistance with SQL Parameters in Power BI - Help with creating a list from SQL Query

Hi @ck18

 

Thanks for your share. Please accept your answer as solution so that other community members will easily find the solution when they get same issue.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.