Hi Daniel,

Welcome to our forum!

If you're loading the data from Power Query to Power Pivot then the relationship is created in Power Pivot. If you want to bring data from the UIDs table to the Detail table (like a VLOOKUP in Excel) before loading to Power Pivot then you can do this by merging the two tables.

Hope that points you in the right direction.

Mynda

Thanks Mynda, I'm super excited about utilizing this new to me resource!

I was able to create the relationship in Power Pivot between the "Property Table" (dim) and the Detail Table like you suggested. Now I need to bring in daily numbers from a different database. If i do the same method I will be loading over 4 millions rows for the time period I need.  

Using Excel 365 & I attached a sample file

Here is my objective:

Since I am able to create a DimTable and filter the table down to the required data set. I think I should be able to reference that query and make a dynamic list of APIWellNumbers to filter down the Daily Records. This way I will not have to bring the entire table into power pivot and then filter off the relationship.

I've tried all afternoon and this is the best syntax I could come up with from google. I just can't make it work

#"Filter Rows by Dynamic List"= Table.SelectRows(Source, each List.Contains(APIFiltered,[APIWellNumber]))

Hi Daniel,

Please see lesson 7.01 of the Power Query course on Parameter Tables for Filtering. If you're still stuck, come back with your file showing your attempt and I can help you further.

Mynda

Attached is a sample data set with my second attempt. I was able to make the parameter work but for only a single entry from a pasted list. I'm trying to limit the number of Distinct APIWellNumbers in the daily table from ~3,400 to match the DimTable's of 981 and then create a table of their daily production. 

I understand that this can be done in power pivot for this data set. The SQL server I will be pulling these daily numbers from holds all APIWellNumbers for the entire company. To keep the refresh time reasonable I need a way to filter from a dynamic list of desired APIs instead of bringing in the whole table into Power Pivot and then filtering.  

Hi Daniel,

If you want to filter by multiple parameters you need to create a list, which you've done with APIFiltered, then you can use the List.Contains function in the FactTable_Parameter-Pasted query. In the last step of that query, Filtered Rows, use this formula:

= Table.SelectRows(#"Removed Columns", each List.Contains(APIFiltered , [APIWellNumber]))

i.e. where the APIFiltered list contains the ID in the APIWellNumber column of the FactTable_Parameter-Pasted query.

Hope that helps.

Mynda

I was dancing all around that syntax but couldn't get the order right. Thank you