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

Sorry, repeating as my first question was marked as spam. I removed the link which I assume is why my post was removed.

 

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. 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, Production is the schema,

ProductCategory is the table and "Name" is the field I want to create a list from.

 

Update: I updated my source by clicking on the exact table I was referencing and my code has been changed to correctly reflect the dbo and table. New code below:

 

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

New Error Message:

 

Expression.Error: We cannot convert the value "Name" to type Function.
Details:
    Value=Name
    Type=Type

But I am encountering the new error above.

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Not sure how your data exactly looks like, but you can try this one as a start:

 

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
ImkeF
Super User
Super User

Not sure how your data exactly looks like, but you can try this one as a start:

 

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

ck18
Frequent Visitor

I’m having trouble accepting your solution but it worked for me.
ck18
Frequent Visitor

Thank you. I didn’t understand what the parameters for the Table.AddColumn functions were and I was trying to use an example I saw online as a template. Where I wrote “Name”, I was expected to put a function. That’s why I was getting a type error. each [Name] was the function I needed to fill the column.

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.