Retsricting dimensions by fact records

The simple question is if I bring in a dimension with 200,000 items and only say 10,000 of them link to my fact table how do I get rid of the items that dont link from the model?

I am not happy with this approach myself and it is being pushed on me so
please dont say it could be done in the SQL query
please dont say do it before you import the data

I am stuck with- it must be done within the data set.

Only good thing about working remotely is they cant see you bang your head against the wall when they suggest silly ideas.

Thanks in Advance

Do they share a single key ? If so then create a step, something like:

Table.SelectRows( Source, each List.Contains( Fact[Key], [DimensionKey] ))

Source = Previous step name, see the Applied Steps pane
Fact[Key] = Query name and Column name that contains the Key
[DimensionKey] = Column reference to the Key in the table you are filtering.

I hope this is helpful.


If there are performance issues add a List.Buffer step like so:

Source = Source,
BufferedList = List.Buffer(  Fact[Key] ),
FilterRows = Table.SelectRows( Source, each List.Contains( BufferedList, [DimensionKey] ))
1 Like

will look at that as soon as I can finish a couple of issues left for me.

Hi @ells, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Thanks, it looks like its working it. I will have to try this on a smaller sample of data. When I applied the change to the data set it sat there applying the change, I watched as it loaded the fact and in the end I had to kill it.

Many Thanks