cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SH-VE
Helper III
Helper III

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

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

jborro
Solution Sage
Solution Sage

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
jborro
Solution Sage
Solution Sage

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

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

jborro
Solution Sage
Solution Sage

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.

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

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

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. @jborro 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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors