replace one table with a merged identical table

[12Bowers12]

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

[BA_Pete · Accepted solution]

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

[12Bowers12 · Accepted solution]

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

[BA_Pete · Accepted solution]

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

[12Bowers12 · Accepted solution]

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

[BA_Pete]

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

[12Bowers12]

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

[Greg_Deckler · Accepted solution]

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

[12Bowers12]

Thank you so much, Greg,

Sorry for the confusion.

Have a nice weekend.

Dennis

[12Bowers12]

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]

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

[12Bowers12]

Greg, 

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

Please be advised.

Dennis

[12Bowers12]

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

[Greg_Deckler]

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

[12Bowers12]

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})