Using parameters in SQL query

#1

Hello,

I’m working on a report to visualize data about some videos my team owns. I’m using a SQL query in the query editor to connect to a Redshift database that contains information for thousands of videos. Each video in the database is assigned a unique video ID and each play of a video generates a row in the database.

The problem: The Redshift database stores data about thousands of videos and contains millions of rows. My team is interested in a relatively small subset of videos (about 600 videos right now and we add more every month). Adding each of our video IDs to the SQL query in the query editor and manually updating each month doesn’t scale.

What I want to do: Add a parameter to the SQL query that will import only the rows where the video ID value in the Redshift database = any of the values in the video ID column of my team’s catalog.

What I’ve tried: I created a Catalog query with a column that contains the video IDs for all the videos my team owns. The source of the Catalog query is an Excel file stored on a SharePoint. And I’ve added a parameter to the SQL query to filter on a single value from that Catalog query.

#(lf)AND action=‘PLAYED_VIDEO’#(lf)AND value=’"&VideoIDs&"’;")

I can set the value in the parameter to 12345 and the SQL query will get the data only for video ID 12345. That works fine. But I can’t figure out how to modify the M code to get the data for the entire catalog.

The question: Is it possible to create a parameter for the SQL query that will pass all the video IDs from the Catalog query?

#(lf)AND action=‘PLAYED_VIDEO’#(lf)AND value=’"&[any value in the VideoID column of the Catalog query]&"’;")

Thanks,
Nic

#2

Hi There

One option I see to achieve is the following :

  • pull in data from your sharepoint excel file as a table in Power BI which will have video id’s that you need to use to filter your master table.

  • Now Import your master data table with all data and then apply a Merge Query inside Query Editor with Inner Join as the Join Kind (refer attached screenshot below ) and this should filter down your master table down to the video id’s based on your catalogue excel file.

  • This way every time you update your excel file in Sharepoint, you can just refresh your powerbi Model and it should update your master table automatically

snip1
snip1.jpg1500×1455 200 KB

Let me know if this helps.

Thanks
Raminder

#3

Hi, Raminder.

Thank you for taking the time to look at my problem. If I understand your solution correctly, I would still be pulling in millions of extra rows only to remove them with the inner join. My goal with adding the parameter is to avoid pulling in those unnecessary rows.

Please let me know if I’ve misunderstood.

Thanks again,
Nic

#4

Hi @Nic

The trick is to

  1. Create Custom function in Power Query to extract data from main table and passing Category values as parameter.
  2. Call the Custom function for each row of Category table using Add Column --> Invoke Custom function.

I have added a sample file, where

  1. Created a Base table Catalog having only ProductSubcategoryKey.
  2. Created a Custom function fnExtractDetail, to extract details from ProductKey table by passing SubCategoryKey as parameter
  3. Added a Column to the BaseTable by Invoking a Custom function and passing SubProductKey Column as parameter.

Please find sample PBIX file below. If not clear, then I will try to find some link and share.

Nic_Solution.pbix (368.1 KB)

Thanks
Ankit Jain

#5

Hi, Ankit.

Thank you so much for the detailed recommendation. I can see the M code in the queries, but I can’t run them because I don’t have credentials.

If you can find a link to share without too much trouble, that would be great. Or perhaps screen shots of the previews for the queries?

With appreciation,
Nic

#6

Hi @Nic

Check this video from Guy in a Cube. He has explained in very easy manner. Let me know if still not clear.

https://www.youtube.com/watch?v=iiNDq2VrZPY

Thanks,
Ankit Jain

2 Likes
#7

A million thanks to you, @ankit! The video was easy to follow and your solution worked perfectly!

All the best,
Nic