Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 @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
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.
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
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.
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. A
m I missing something here?