Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hasenclever
Helper II
Helper II

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

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:

materiallocationmaterial_descyear_monthforecast
abs123store_01iphone2024_0110.000
dfg345store_01samsung2024_025000

 

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

 

materiallocationmaterial_desc2024_012024_02
abs123store_01iphone10.000null
dfg345store_01samsungnull5000

 

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
1 ACCEPTED SOLUTION

10 REPLIES 10
hasenclever
Helper II
Helper II

ticket opened to the support. thanks @lbendlin 

hasenclever
Helper II
Helper II

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

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
Super User
Super User

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
Helper II
Helper II

@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
Super User
Super User

 

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.

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.

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

And is there a way of cleaning the cache?

Probably.  No idea where this is for Excel though.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors