Part2: [Expression.error] There weren't enough elements in the enumeration to complete the operation.
Changed column names are frequent reason why the query breaks. This example works very nice for Excel source tables. We must do it in the step after PROMOTED HEADERS. This Excel table serve as a model.
I have loaded my table into Power Query Editor and want to make sure that after some users change the column names the data will be still loaded correctly. I will show you an example how to do it with multiple columns not just with one column. But anyway, I need to keep the number of columns in some reasonable figure, can’t show you how to do it with 50 columns. But the process is very similar.
After the table got loaded into Power BI, click on Promoted Headers in the APPLIED STEPS section and go to Advanced Editor. We are going to create a function which takes Promoted Headers step as an argument and index which starts from 0 and represents columns. You can use any name for your function. Promoted Headers works similar as LIST or ENUM in other programming languages. (C#,Visual Basic...etc)
KissMyAss function returns the names of the headers. Holds a list of column names coming from Excel table.
If you click on the function in the APPLIED STEPS section, you can see the values it holds. {0} refer to the first header in our list. If you change the index number function will refer to different header.
Now click on the Changed Type in the APPLIED STEPS section and insert the function.
USE
Table.ColumnNames(#"Promoted Headers"){0} for "Locations[a]"
Table.ColumnNames(#"Promoted Headers"){1} for "Cases[b]"
Replace all 4 hardcoded column names within the function using correct index value{}.
After you finish it should look like this:
In between I’ve applied couple of new steps and correction on the table. I’ve removed one column. You don’t need to use this function in that step. But I’ve also renamed the columns and the function should be use for this step. Now repeat the process for RENAMED COLUMNS step.
Result will look like this. I’ve also marked the steps where I used the function with a red circle [RENAMED COLUMNS, CHANGED TYPE].
Don’t forget to SAVE any changes in Excel before you start to load the data. Otherwise it won’t appear in Excel. If you have any question, do not contact me. Instead of that keep practicing!
Part2: [Expression.error] There weren't enough elements in the enumeration to complete the operation.
After 6 months working with this data source from Wikipedia it has returned this error message: [Expression.error] There weren't enough elements in the enumeration to complete the operation. My table stopped updating. How did I fix it?
How you can see from wikitable the number of cases should be 4M and in my excel table I have only 3.9M. So, it is obvious that the data are not updating.
Open Power Query Editor.
This is how it looks like now. Yellow triangle with the exclamation mark next to the table Wiki means something is really fucked up.
2. Basically, what have I done was that I decided to change the URL address. Seems to me like it was modified.
All you have to do is to copy the URL from web site and paste it in the source step, the very first one.
So now it is fixed up. Just couple of simple errors left to be corrected.
Replacing Errors with null. You can replace them with zero...
click Close&Load
How you can see from this picture replacing the old URL helped to fix this error.
