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

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 IX
Super User IX

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!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

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 III
Helper III

Re: Power BI Desktop ; Working with multiple files

@amitchandak thank you for all the information!

 

Regards

Marcelo Nascimento

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors