Power BI Desktop ; Working with multiple files

[datanau001]

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 

[amitchandak]

@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-Rel...
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-My-Power-BI-report-is-slow-What-sho...

[datanau001]

@amitchandak thank you for all the information!

Regards

Marcelo Nascimento

[jdbuchanan71 · Accepted solution]

@datanau001 

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.

[datanau001]

@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