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

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Power BI Desktop ; Working with multiple files

@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.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Power BI Desktop ; Working with multiple files

@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.

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Power BI Desktop ; Working with multiple files

@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...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: Power BI Desktop ; Working with multiple files

@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

Highlighted
Helper II
Helper II

Re: Power BI Desktop ; Working with multiple files

@amitchandak thank you for all the information!

 

Regards

Marcelo Nascimento

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors