Error in service with table being pivoted

[BIanon]

Hello Community,

I have a table like this:

Id, Key, Value

In Power Query I pivot the key and value pairs to create columns from them.

My issue is, every time a change happens in the table, the pivoted table fails in the service. I can then open up the .pbix file, refresh it locally without issue, re-upload and it can refresh in the service again until this table is changed.

If there was an error that error should also happen locally, so not sure how i can avoid this or what to look for.

[BIanon]

Why?

[lbendlin]

Because that's how Power Query is designed. It does not tolerate dynamic query meta data changes.

[BIanon]

All right thank you for explaining this

I would like to understand this limitation more in depth but am unsure where I can read more about it. I tried searching the words you used but could not find a match in the documentation

[lbendlin]

Don't pivot.  Load the data as is into Power BI and let the visuals do the pivoting for you.

[BIanon]

Not an option in my case.

[lbendlin]

Then you will have to live with these issues.  Every time you change the Power Query Meta Data that will require a re-publish.

[BIanon]

What metadata?

The M code doesn't have hard coded coulmn names

Is this what you are referring to when you say metadata?
https://bengribaudo.com/blog/2021/03/17/5523/power-query-m-primer-part20-metadata

Is it not possible AT ALL to use pivot operations and push to service?

[lbendlin · Accepted solution]

The M code doesn't have hard coded coulmn names

That's pretty much your problem.  The output of a M query (what gets loaded into a Vertipaq partition) is expected to have exactly that, hardcoded column names.

[BIanon]

all right thanks

still lots to learn it seems