Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a file that is around 5.5mb. The main table in it is 285k rows, containing products by location and date. There is a second table of around 18K rows containing customer data per product.
Product codes are not unique in either table, so I joined them in M to avoid issues with many to many relationships
When I combine the two together in powerquery the table increases to 1.4 million rows and the file size increases to 45mb.
I am concerned about refresh times and the overall size of the file. The dashboard itself is still quite responsive. It's more the refresh I'm trying to speed up. What can I do to reduce the file size and keep it quick?
Or is there a better way to combine the tables that gets around the many to many relationship issue?
Solved! Go to Solution.
Hi @dapperscavenger ,
By the sounds of it, you don't need to join these tables at all, you need a dimension table.
In Power Query, create a products dimension as follows:
1) New Source > Blank Query
2) In the formula bar, enter:
= Table.Distinct(Table.SelectColumns(yourTableWithAllProductsIn, {"ProductCode", "ProductName"}))
Where "yourTableWithAllProducts" is the name of one of your queries that contains all products, and "ProductCode" and "ProductName" are the names of the columns from this query that you want to retain in your dimension table.
Then Apply this to the data model.
Once your two original (unjoined/unmerged) tables are in the model with your new products dimension table, make relationships between the dimension table and each fact table as follows:
dimension[ProductCode] to fact[ProductCode] - One to many respectively.
You can now use the ProductName field from your dimension table in any visuals and this will correctly filter the fact values in your two fact tables.
Pete
Proud to be a Datanaut!
Hi @dapperscavenger ,
By the sounds of it, you don't need to join these tables at all, you need a dimension table.
In Power Query, create a products dimension as follows:
1) New Source > Blank Query
2) In the formula bar, enter:
= Table.Distinct(Table.SelectColumns(yourTableWithAllProductsIn, {"ProductCode", "ProductName"}))
Where "yourTableWithAllProducts" is the name of one of your queries that contains all products, and "ProductCode" and "ProductName" are the names of the columns from this query that you want to retain in your dimension table.
Then Apply this to the data model.
Once your two original (unjoined/unmerged) tables are in the model with your new products dimension table, make relationships between the dimension table and each fact table as follows:
dimension[ProductCode] to fact[ProductCode] - One to many respectively.
You can now use the ProductName field from your dimension table in any visuals and this will correctly filter the fact values in your two fact tables.
Pete
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
79 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |