Forum Discussion
Power BI Desktop ; Working with multiple files
Hello friends,
I have a request which will make me to work with data from 8 excel sheets and in total it wil be around 2million lines.
All this info is now in power bi desktop in separate tables. However, I want to ask your opinion on what would be the best way to deal all this data, I mean: would you recommend to put it in one single table? If so, which would be the best option in your view?
Thank you!
Marcelo Nascimento
Yes, if the data can be combined into a single table that would be the way to go. Use Power Query to pull in each file and to any transformations needed so it can be combined with the other files. You would want these as connections only, no need to load the individual tables to the model. Once you have them connected and transformed you can do a union query to combined all the 8 sources into 1 table an that is the one you load into your data model.
4 Replies
Yes, if the data can be combined into a single table that would be the way to go. Use Power Query to pull in each file and to any transformations needed so it can be combined with the other files. You would want these as connections only, no need to load the individual tables to the model. Once you have them connected and transformed you can do a union query to combined all the 8 sources into 1 table an that is the one you load into your data model.
jdbuchanan71 Thank you for your reply!
I also noticed that after all needed transformations appending the queries into a new query is a possiblity can help.
Thank you again.
Marcelo Nascimento
datanau001 , if they have the same structure you should merge them together.
Other consideration
1. Is data queries together
2. Are there some sheet fewer accesses.
3. If we keep them separate how many formulae/measures we need to add to combine if need combine data.
Based on the above question take call. Having fewer data in one table is helpful if I do not need to add data from other tables too often .
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
also, refer if these can help
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-The-Do-s-and-Don-ts-of-Power-BI-Relationships/td-p/712566
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-My-Power-BI-report-is-slow-What-should-I-do/td-p/712567
