General advice on best way to handle large dataset

[dapperscavenger]

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?

[BA_Pete · Accepted 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