cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

General advice on best way to handle large dataset

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

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

View solution in original post

1 REPLY 1
Highlighted
Super User I
Super User I

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors