Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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?
Solved! Go to 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
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
Thank you very much for the answer, it worked.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
7 | |
6 | |
5 |