Use a data table to create an SQL query

[waynewoodhead]

Hi,

Using PowerQuery in PowerBI.

I have an SQL database table that I want to read in, and then use the information in it to create a follow on query into another database.   IE

I have a rental contract table with the names of items that have been rented.   I want use this table to read in the item names associated with a particular contract, and then use this list to get activity data about the items from another table.

eg 

Contract table

Contract Number     Item Name    Start Date      End Date

1                                 item1             1/1/2019        2/1/2019

1                                 item 2            1/1/2019        2/1/2019

1                                 item 3            1/1/2019        2/1/2019

2                                 item 3             2/2/2019       5/2/2019

Item table

Item                           Date         Usage

Item 1                       1/1/2019    50%

Item 1                       2/2/2019    25%

Item 1                       3/2/2019     10%

Item 2 ......

SQL query something like    get data where Item = " list of items" and date > Contract Start Date and date <contract End date.

How do I make sure I complete the first table read first, then use the data to drive the second SQL statement?

Thanks

[v-juanli-msft · Accepted solution]

Hi @waynewoodhead 

If you can accept to import two tables into Power BI firstly, then transform and filter to get your expected result, you could follow below:

For both table, to get clear and standard data, please firstly use  "Trim" and "lowercase" under the Transform->Format.

Next, in the Table2(Item Table),merge query with Table1 based on "item name" column,

then expand "start date" and "end date",

create a custom column to find which rows satisfy your condition.

Custom=if [Date] >= [#"Table1. Start Date"] and [Date]<=[Table1.End Date] then 1 else null

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

1. Connect to all the tables you need. Do NOT use the Advanced SQL statement section, just load the tables. Right-Click on each query and uncheck "Enable Load." We'll do that later.
2. Transform the first table as desired to get whatever contract data you want.
3. Reference the first table (right-click, create reference) and remove all but the Items column, and then right-click on that and to eliminate duplicates (DISTINCT)
4. Go to table #2. Merge it with the query you crated in step 3 using INNER JOIN on the item number. Expand the step 3 item query, then delete the column. Don't delete the merged column step. Expand, then delete. This will preserve folding. Don't ask me why deleting the merge step breaks folding.
5. Continue massaging data as needed.
6. At the end, right-click on what you really want to load in, and load to DAX.

100% of the above will fold, meaning your SQL server will do all the work and minimize data transmission. You may include other steps in step 5 that break folding, like many of the Text.*() functions, but that is ok. Save those to the end if possible. I've created 800+ line SQL statements via folding and combing/grouping/transforming tables from SQL Server.

Don't worry about the order you do it in. Power Query keeps track of dependencies. So if Query #4 requires Query #1, it will process 1 before 4. If it is foldable, it will actually create a single SQL statement that encompasses Query 1, 2, 3, and 4 in one big hairy statement.