cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ck18
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

 

@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

View solution in original post

5 REPLIES 5
v-cherch-msft
Microsoft
Microsoft

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.

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

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.

 

@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

View solution in original post

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.