Forum Discussion
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,
KellyDid 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.
edhans- Thank you very much. I will consider and experiment with your suggestion.
I understand VALUES is not the best option, at first i was just surprised by the total MB of the data model. Usually I am not Power BI user, but Power Pivot. And when i create similar dim tables from a flat file Excel loads only the rows I told Power Query to filter. So the cardinality of the tables is low and performance is ok. I was truly shocked to see table with 15 rows in it to be 46MB.
Community ChampionAnonymous 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.