Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |