Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
Hi @ck18
Here is the document for you:
https://www.excelguru.ca/blog/2016/04/28/pass-parameters-to-sql-queries/
Regards,
Cherie
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
@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
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