cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
12Bowers12
Resolver II
Resolver II

replace one table with a merged identical table

Good afternoon, everyone,

I have an Excel file with 3000 rows and 6 columns. I need to copy and paste this Excel file into Power BI so there will be no any connection between Power BI and the Excel data source.

Since Power BI limits the copy and paste to 3000 cells, I split the Excel files into a few parts. Then I copy and paste these small Excel files into Power BI and merge these tables using Appdend Queries approach. Until this step, it works perfectly.

 

Now I need to use this merged table to repace another indentical table named [New York] and then delete all these tables including the merged tables. I just could not go through this step.

 

Appreciate your help.

 

Dennis

 

3 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

Hi @12Bowers12 ,

 

I've had a look through your conversation with @Greg_Deckler and feel that you both may be missing a trick here.

I am assuming that you want to copy and paste your data from Excel to PBI because you don't want PBI data to change if the Excel workbook is updated and saved, right?

If so, then create a new query connecting to your Excel file as normal. When the table has loaded, right-click on the table/query name and uncheck 'Include in report refresh'. This will prevent this query from going back to the Excel source and getting updates.

Call this new query 'New York' and you're done.

 

Apologies if I've misunderstood your requirements.

 

Pete

View solution in original post

Thank you, Pete,

Based on your advice, I uncheck 'Include in Report Refresh' to cut off the dependency on the Excel data source. Since there is an existing table 'New York', I just delete the existing table 'New York' and rename the new table as 'New York'. Then I recreate the visuals based on the new table 'New York'.

The reason I want to cut off the dependency of the table 'New York' on the Excel data source is that the clouds don't allow to set up scheduled refresh due to credential issue.

By the way, what's difference between 'Include in Report Refresh' and 'Enable Load'?

Appreciate your help and Greg's help.

Dennis

 

 

View solution in original post

@12Bowers12 Include in report refresh prevents the query from reaching out to the source. Enable load loads the data into the data model. So, for example, for intermediate queries that feed a main query, you want to disable load but leave the include in report refresh so that those queries still feed the data to the main query. And @BA_Pete is correct about turning off refresh query, I guess I didn't go there because the question was about copying and pasting.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

12 REPLIES 12
BA_Pete
Super User
Super User

Hi @12Bowers12 ,

 

I've had a look through your conversation with @Greg_Deckler and feel that you both may be missing a trick here.

I am assuming that you want to copy and paste your data from Excel to PBI because you don't want PBI data to change if the Excel workbook is updated and saved, right?

If so, then create a new query connecting to your Excel file as normal. When the table has loaded, right-click on the table/query name and uncheck 'Include in report refresh'. This will prevent this query from going back to the Excel source and getting updates.

Call this new query 'New York' and you're done.

 

Apologies if I've misunderstood your requirements.

 

Pete

View solution in original post

Thank you, Pete,

Based on your advice, I uncheck 'Include in Report Refresh' to cut off the dependency on the Excel data source. Since there is an existing table 'New York', I just delete the existing table 'New York' and rename the new table as 'New York'. Then I recreate the visuals based on the new table 'New York'.

The reason I want to cut off the dependency of the table 'New York' on the Excel data source is that the clouds don't allow to set up scheduled refresh due to credential issue.

By the way, what's difference between 'Include in Report Refresh' and 'Enable Load'?

Appreciate your help and Greg's help.

Dennis

 

 

View solution in original post

Hi @12Bowers12 ,

 

Glad this has worked out for you.

Did you accidentally mark your own response as the solution instead of my answer, or was there a specific reason my answer didn't work for you?

 

Pete

Thank you so much for help, Pete, it works perfectly now! Have a good day.

@12Bowers12 Include in report refresh prevents the query from reaching out to the source. Enable load loads the data into the data model. So, for example, for intermediate queries that feed a main query, you want to disable load but leave the include in report refresh so that those queries still feed the data to the main query. And @BA_Pete is correct about turning off refresh query, I guess I didn't go there because the question was about copying and pasting.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Thank you so much, Greg,

Sorry for the confusion.

Have a nice weekend.

Dennis

12Bowers12
Resolver II
Resolver II

Thank you, Greg,

Do you mean to uncheck "Enable Load" on table [New York]?

I don't want to keep the tables from copyed/ pasted from Excel and  the newly  merged table in Power BI.

Dennis

 

 

 

Greg_Deckler
Super User
Super User

@12Bowers12 Don't delete them, right-click the queries and uncheck "Enable load"


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Greg, 

If I uncheck Enable Load on table [New York], all the visuals related to table [New York] are disabled.

Please be advised.

Dennis

 

Greg,

This question is actually related to other issue:

How to copy and paste an Excel files with more than 3000 cells into a Power BI table?

Dennis

@12Bowers12 No, just disable load on the intermediate queries where you pasted them in. You an replace the New York query by taking the query that appends all your intermediate queries and opening Advanced Editor. Ctrl-A, Ctrl-C. Now open the New York query, Ctrl-A, Ctrl-V. 

 

To copy and paste more than 3,000 cells. Short answer is that you don't.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Thank you, Greg,

I have disabled load on the intermediate queries on tables 2-6. Then I copied and pasted the query from the merged table to table [New York].

Then I delete the temporary merged table. But when I tried to delete tables 2-6 but got denied as table [New York] query appends the five tables. I don't need the five intermeiate tables.

 

#"Appended Query" = Table.Combine({#"Changed Type", Table2, Table3, Table4, Table5, Table6})

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors