Making Data Model From Flat File

Hi  Anonymous,

How about creating the dim tables with dax formular rather than in power query?

For example,my oringinal table is as below from an Excel file:

To achieve what you need:

Create dim tables as below:

Table = VALUES(Sheet2[category])

Then create relationships as you need:

Pls note that the direction should be from fact table side to dim table side.

Then you would see when you refresh the table,it will only load one time:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Anonymous It is not loading 46mb. It is processing the 46mb file to load the data. The actual 15 record table is probably a few KB at most. You could validate this using DAX Studio and the Vertipaq Analyzer feature which will tell you the size of each loaded table. 

this is an issue working with flat tables. With no database on the back end power query has to fully process the entire file even if you have filtered it to just one record. 

But it is best practice to do this in PQ. Consider if your csv file has the customer number and name in it.

You could create a DIM table of that info, but by using DAX, you cannot remove that redundant customer name info from the FACT table. You can in power query. Smaller model over all.