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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors