CODEWITHSUNDEEP
excelvbapowerbipowerquerypowerpivot
Laurent Ress
Laurent Ress

Reputation: 37

Refresh power pivot-power query

I have a table in power query that is fed from some Excel files, with this data I make an inner join with other catalog tables that I have and do operations on calculated columns and then add to the power pivot data model to make some pivot tables, initially everything was working very well until I made adjustments to the power query table by removing or adding more columns as well as editing the inner join operations, now when I do an update with and I want to pass the power query table to data model power pivot it gives me an error that the table does not exist, Mention that if I do the update only power query table it works without problems, the problem is when the data is going to be passed to power pivot.

How can I correct this error?

Sorry for my English

Upvotes: 0

Views: 863

Answers (3)

Alexander Sandoval
Alexander Sandoval

Reputation: 21

Alex here

It seems the issue may arise from potential duplicate primary key values as your dataset grows. My suggestion is to go to Power Query, select the primary key column, and remove duplicates from the table you've chosen as the DIM (dimension) table.

Here's an example to illustrate this:

Let's say you have two tables, one containing transactional data with events per country and another with a DIM (dimension) table of countries with unique values for each step.

Transactional table ("TransactionTable"):

EventID Country EventValue
1 USA 100
2 Canada 150
3 USA 120

DIM table ("CountryDIM"):

Country Region Population
USA North 330
Canada North 38

Assuming "Country" is your primary key, go to Power Query, select the "Country" column in the "CountryDIM" table, and remove duplicates to ensure uniqueness.

In your scenario, this process helps avoid potential issues related to duplicate primary key values when updating the Power Pivot data model. Ensure the primary keys maintain uniqueness to prevent errors during the update.

I hope this guidance helps resolve the issue you're experiencing. If you have further questions or need more assistance, feel free to ask. Good luck!

Upvotes: -1

Lixin Li
Lixin Li

Reputation: 43

I think it depends on the structure of your code, if you "hard code" the steps, whenever you remove, but especially add columns, it gives you an error. You need dynamic columns. I suggest you watching this video. It might gives you some idea about the structure of the code. https://www.youtube.com/watch?v=Z3r9JbW9QUs&list=PL5NlUYOM2iOgYQCf0HJlwKnsXvun40YIm&index=12

Upvotes: 0

BirdMan
BirdMan

Reputation: 171

Yes, often when changes are made to initial queries these issues happen. Normally, when Query Names and/or Field/Column Names are changed and these names are used in the Merge or calculation steps query will pop these errors. So, review/compare all the changes that you made to steps after the merge. If you don't find any errors, consider making a copy of the steps and rebuilt from the merge to ensure optimum performance.

Upvotes: 0

Related Questions