Data reduction in dimension tables

[flaviosouzaab]

Hi,

I have an Excel spreadsheet that connects to a SQL Server database to obtain data from a DW, my difficulty is in keeping in the dimension tables only the records that are present in the fact table, users see different data when accessing the spreadsheet, so the Dimensions should reflect the data according to what is in fact.

My attempt was as follows (I'll give an example with the customer dimension):

1) I create a table in buffer with the ids of the clients that are in fact.

2) In the customer dimension I do an inner join in this buffer table created above so that the dimension only has the keys existing in the fact.

But when I do that, the data loading takes a long time, the impression I have is that the fact table is loaded in each dimension over and over again.

How can I do this type of transformation in power query?

[watkinnc · Accepted solution]

If these are from the same database, a mere inner join from dimension to fact would normally be your best option (without the buffer, which causes the problem you describe above.

You could also try a buffered list instead of table, by making a query that gets the distinct values from the customer column of the fact table, like List.Distinct(Fact[Customer])

Name that query FilterValues.

Now when you make your fact table query, filter like:

Table.SelectRows(PriorStepOrTableName, each List.Contains(List.Buffer(FilterValues), [CustomerColumnNameFromThisFactTable]))

--Nate

Try using a buffered list instead of a buffered table:

Table.SelectRowsFactTabls

[flaviosouzaab]

Thank you very much for the answer, it worked.