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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SH-VE
Helper IV
Helper IV

Expression.Error: The column 'Column15' of the table wasn't found. Details: Column15

Hi All, 

 

I am sourcing an a monthly budget file from Sharepoint in my report . The file conatins quantity ordered by customer for different part# for coming months. The file was updating properly on the report , but suddenly stop refresh and got the error : Expression.Error: The column 'Column15' of the table wasn't found.
Details:
Column15

Column 15 here is Jan 2021. I checked on sharepoint file , the date row is 'date format' and numbers are 'number format'. Can someone share thoughts on what could be the reason.

Snag_1b3974a.png

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi @SH-VE ,

 

well if the number of columns changes frequently, please use this code:

 

    FormatFields = List.Transform(List.RemoveItems(Table.ColumnNames(#"Removed Blank Rows"), {"Customer","Supplimental Part  #"}), (x)=> {x, type number}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Customer", type text}, {"Supplimental Part  #", type text}} & FormatFields),

 

 

You need to open the query in the Advanced Editor and replace the entire line starting with #"Changed Type" with the lines above.

 

Alternatively, just delete the "Changed Types" step entirely and check if your PBI model will tolerate it when you load the data.

 

Kind regards,

JB

View solution in original post

Anonymous
Not applicable

Hi @SH-VE ,

 

precisely, what @Jimmy801 said:

1. The error was caused by using the Column name which is no longer exists. Typically, it happens when PBI generates some steps for you and then you insert something in the middle of the generated series. Because unlike Excel, PBI has mutable column names, you need to be careful and track when they change. This is similar to a named region in Excel, if you use one in a formula and then delete the name, the formula will fail to execute.

2. As explained by Jimmy, I am automating the formatting step in your query, by assigning all but the first one to type number. This is an advanced technique and, while you can replicate this approach in your other queries, explaining how this works exactly may be quite wordy. If you are interested in understanding the PQ and M-language I would recommend this series of articles: https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi.... This is a good starting point for the in-depth overview of the semantics. Of course, reading solutions on this forum is also a very good way to learn valuable things.

 

Kind regards,

John

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Hi @SH-VE ,

 

precisely, what @Jimmy801 said:

1. The error was caused by using the Column name which is no longer exists. Typically, it happens when PBI generates some steps for you and then you insert something in the middle of the generated series. Because unlike Excel, PBI has mutable column names, you need to be careful and track when they change. This is similar to a named region in Excel, if you use one in a formula and then delete the name, the formula will fail to execute.

2. As explained by Jimmy, I am automating the formatting step in your query, by assigning all but the first one to type number. This is an advanced technique and, while you can replicate this approach in your other queries, explaining how this works exactly may be quite wordy. If you are interested in understanding the PQ and M-language I would recommend this series of articles: https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi.... This is a good starting point for the in-depth overview of the semantics. Of course, reading solutions on this forum is also a very good way to learn valuable things.

 

Kind regards,

John

Thank You for providing the explanation. It was very helpful.

Anonymous
Not applicable

Hi @SH-VE ,

 

In this case, PQ is actually looking for the column with name = "Column15", not just 15th column in the dataset.

I suggest deleting the "Changed Types" step entirely and re-type columns manually.

 

Kind regards,

JB 

Thank You for the response. 

Can you please ellaborate on re-type columns ? The columns are getting sourced from Sharepoint and gets updated/ changed almosty everyday. Cahnges can range from adding new forecast month column to quantity numbers.

Anonymous
Not applicable

Hi @SH-VE ,

 

well if the number of columns changes frequently, please use this code:

 

    FormatFields = List.Transform(List.RemoveItems(Table.ColumnNames(#"Removed Blank Rows"), {"Customer","Supplimental Part  #"}), (x)=> {x, type number}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Customer", type text}, {"Supplimental Part  #", type text}} & FormatFields),

 

 

You need to open the query in the Advanced Editor and replace the entire line starting with #"Changed Type" with the lines above.

 

Alternatively, just delete the "Changed Types" step entirely and check if your PBI model will tolerate it when you load the data.

 

Kind regards,

JB

Hi @Anonymous  Thanks for you response . This did help and the model is refreshing fine now.

 

However, I am still wondering what the problem was and how the query above resolved it. Are you able to share some insight on this. It will be of great help in my learning as I am still learning. 

Jimmy801
Community Champion
Community Champion

Hello @SH-VE 

 

the problem here was that you were reading of an Excel-sheet that had a lot of empty columns. So they were al registered. Now, as your data is growing the anonymous columns like "Column15" are not there anymore, but they are substituted by your real data. Therefore this is running into an error. @Anonymous s solution formates every other column then your first 2 as number in a dynamic way.

 

Hope I was clear enough

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Thank You.

Thanks,

It does not work without "Changed Types" step.

 

I tried the syntax you shared, getting this error. ASnag_1eb4bd9.png

 

m I missing something here?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors