Error pivoting columns ("The column of the table wasn't found.") that doen't make sense

[hasenclever]

Hi guys,

I have a problem that I cannot understand. I am working on the service with dataflows and I have a simple table that brings me sales forecast for a every month. So the normal table is like this:

material	location	material_desc	year_month	forecast
abs123	store_01	iphone	2024_01	10.000
dfg345	store_01	samsung	2024_02	5000

Then I ask to pivot the year_month column to chage the table to this:

material	location	material_desc	2024_01	2024_02
abs123	store_01	iphone	10.000	null
dfg345	store_01	samsung	null	5000

And it works fine. No errors.

But then I schedule the dataflow to be refreshed every day. Then I started to get the error Expression.Error: The column '2024_01' of the table wasn't found.

But the code on the power query does not make any mention to the column name, nevertheless I get the error.

Is there any workarounds?

et

    Source = monthly_fcst_own_stores,

    Sorted rows = Table.Sort(Source , {{month_number, Order.Ascending}}),

    Pivoted column = Table.Pivot(Table.TransformColumnTypes(Sorted rows, {{month_number, type text}}), List.Distinct(Table.TransformColumnTypes(Sorted rows, {{month_number, type text}})[month_number]), month_number, CommFcst)

in

    Pivoted column

[hasenclever]

ticket opened to the support. thanks @lbendlin 

[hasenclever]

@lbendlin I don't understand the "manually". I have done it programmatically. I am using the pivot transformation function and considering the traditional function, when you pivot, there is a column that is the Attribute and anotther for the Values. I am just doing that. And the attributes that exists are variable. And the pivot function is failling. 

I cannot think of any workaround, thus the cry for help.

[lbendlin · Accepted solution]

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

[lbendlin]

Find a process where you don't need to manually pivot rows into columns. For example in Power BI the visuals do that for you, for free.

[hasenclever]

@lbendlin thanks for your effort, but the cache problem do not solve my issue. Basically, with this error I cannot have a dataflow to prepare a set of data where I need to pivot rows into columns.

[lbendlin]

Then I ask to pivot the year_month column to change the table

Please explain your reasoning for doing this in Power Query.  You should leave the pivoting to the Power BI visuals. You should avoid dynamic columns in Power Query output as much as possible.

[hasenclever]

Basically, I am using the Power Query on Excel. And the user wants to see the outcome values pivoted into columns.

This is a business necessity, I just need to find out why the pivot instruction sometimes presents errors, and sometimes not.

Technically, the columns of my final query are dynamic, because when pivotting the resulting column names can be different, but I do no execute any part of the power m language connected to the dynamic names.

That is part of the problem. The presented error would make sense, if I had any instruction (or code) that would refer the pivoted column by name. So in my opinion the error do not make any sense.

[lbendlin]

Power Query is caching meta data. You keep changing the meta data. Power query gets confused.

[hasenclever]

And is there a way of cleaning the cache?

[lbendlin]

Probably.  No idea where this is for Excel though.