cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sw123
Helper II
Helper II

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

Hi,

 

I want to remove a column from Power Query, but how am I able to do that, when Power Query does not open the table and only gives this error: Expression.Error: The column 'NimikeVersio' of the table wasn't found.

 

Can someone please help me?

 

Thanx in advance!

22 REPLIES 22
watkinnc
Super User
Super User

You should add the last parameter, MissingField.Ignore, to any column selection or renaming steps, like Table.Remove/RemoveOtherColumns. Then, you can safely remove columns without breaking some of the downstream functions.

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

I would like to try this, but am not so good at this. Could you maybe tell me step-by-step how to do this?

v-yingjl
Community Support
Community Support

Hi @sw123 ,

As watkinnc mentioned, you can refer this sample query:

let
    Source = Table.RemoveColumns(
    Table.FromRecords({[CustomerID = 1, Name = "Bob", Phone = "123-4567"]}),
    "Address",MissingField.Ignore
)
in
    Source

See the introduction of Table.RemoveColumns() function:

https://docs.microsoft.com/en-us/powerquery-m/table-removecolumns 

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

yaak
Helper I
Helper I

Power Query is telling you that this column name doesn't exist in your dataset. Check if you have your column name (including any capital letters, spaces, etc). If the removing column is a step in your query click on a previous step and check what the column name is exactly.

Thanx! Yes, I deleted the column from the dataset, so that is correct, it doesn´t exist. This is why I would also like to delete it from the Power Query.

If you removed it from the dataset then it's no longer in your query...

The error is probably from one of the steps which refers to this column, e.g. Data Type change or Delete column, etc. If you have multiple steps in your query start from the bottom one and click on the next up and see if the error is still there, keep checking the previous steps until there is no error, so this means the step below is refering to this column, then you can just edit the formula to remove reference to this column. If you struggle, paste your full code here (View >> Advanced Editor)

I do struggle, a lot. Here is the full code:

 

let
Source = OData.Feed("http://mobtest:8080/PoweredODataALL"),
D_Nimike_table = Source{[Name="D_Nimike",Signature="table"]}[Data],
#"Removed Duplicates" = Table.Distinct(D_Nimike_table, {"NimikeID"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Nimi", "Name"}, {"NimikeID", "Item ID"}, {"Nimikeryhmä", "Item group"}, {"Nimiketunnus", "Itemcode"}, {"NimikeVersio", "Item version"}, {"Nimikkeen luontipvm", "Item created date"}, {"Ohjaustapa", "Ctrl type"}, {"Perustettu", "Item founded"}, {"Rap ryhmä 1", "Reporting group 1"}, {"Rap ryhmä 2", "Reporting group 2"}, {"Tekn nimi", "Technical name"}}),
#"Extract code from Reporting Group 2" = Table.AddColumn(#"Renamed Columns", "Reporting Group 2 code", each Text.Start([Reporting group 2],4)),
#"Define Case1RG Column" = Table.AddColumn(#"Extract code from Reporting Group 2", "Case1RG", each if [Reporting Group 2 code] = "2005" then 1 else if [Reporting Group 2 code] = "2006" then 1 else 0),
#"Define Case2RG Column" = Table.AddColumn(#"Define Case1RG Column", "Case2RG", each if [Reporting Group 2 code] = "2007" then 1 else 0),
#"Case1RG and Case2RG to integer" = Table.TransformColumnTypes(#"Define Case2RG Column",{{"Case1RG", Int64.Type}, {"Case2RG", Int64.Type}})
in
#"Case1RG and Case2RG to integer"

 

This was made by someone a lot smarter than me, who´s help I no longer have...

 

The fields missing are NimikeVersio and Nimikkeen luontipvm. I did remove them from the sourcedata (I know I shouldn´t have done that, it was an accident) and I am no longer able to get them back. I have tried, but somehow Power BI doesn´t recognize them as the same columns. Is there anything I can do?

So, to be clear, you had these columns in your dataset, removed them and now have you added them back to the dataset or not?

  1. If not, then just delete the reference to these columns in your #"Renamed Columns" step, just delete the text in red: 

#"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Nimi", "Name"}, {"NimikeID", "Item ID"}, {"Nimikeryhmä", "Item group"}, {"Nimiketunnus", "Itemcode"}, {"NimikeVersio", "Item version"}, {"Nimikkeen luontipvm", "Item created date"}, {"Ohjaustapa", "Ctrl type"}, {"Perustettu", "Item founded"}, {"Rap ryhmä 1", "Reporting group 1"}, {"Rap ryhmä 2", "Reporting group 2"}, {"Tekn nimi", "Technical name"}})

 

2. If you added these columns to the datasource then and even if the name looks exactly the same, the Power Query may see something different (I had similar issue before with the column names but I don't know why it has happened). To sort out this problem you can delete the two column name changes as in my previous point and then see if the query works, if it does, then just double click on the column names you want to change and then rename these. This will recognise these columns properly.

Hi,

 

Yes, I tried adding them back to the dataset, since removing them destroyed my report, but I don´t need these columns, I just need the report repaired 🙂 But as you said, Power Query does not recognize these two columns after I added them back to the datasource. 

 

I tried removing the name change in the Renamed columns -step, and that works just fine in Power Query, but when I apply the changes, the report has broken and doesn´t show anything. I had some other reports with this same table and for these tables, this works just fine, but not in this particular report. I think it has something to do with the Case 1RG and Case 2RG built for these column, but this is too implex for me. Do you have any ideas what else I could try?

I can't see any link in the next steps to these two columns, what error message are you getting now when you click on each step and which step gives you an error?

Hi,

 

All the steps after renamed columns are giving the error.

 

sw123_0-1631603814149.png

 

Hi,

 

All the steps after renamed columns are giving the error.

 

sw123_0-1631603814149.png

 

Try adding the parameter MissingField.Ignore to the end of your rename step, like:

end of rename step, MissingField.Ignore)

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Are you sure you have removed the columna rename for that column NimikeVersio? Can you paste your final query code here? 

Hi,

 

If I remove the columna rename, the error disappear in Power Query, but the report breaks. That is why, I can´t do it this way.

what you mean by 'report breaks'? check in the modeling tab if this column is used for any relationship, if yes, then either change the relationship to another column if possible or you would need to have this column back in your datasource, or alternatively, you can create a new ID column in your power query which would be called the same as this required column.

 

Can you share the pbxi file here?

The report breaks = I can't see any of the visuals, it is totally empty. This is a huge report and has something to do with the Case 1RG and 2RG built by someone else, because I haven´t had any problems with other reports. Creating new columns in Power Query with the same names does the same thing (breaks the report).

 

I don´t think I can share the file here, since it includes sensitive data.

 

These columns are not used for relationships.

Ok, so, when you remove these two columns from the Rename columns steps, then the Query works fine but you have an issue with the visuals, right?

 

So, in this case I would check first if you have the data loaded from this query into the model (e.g. on the Data panel), then check one of the visuals and see what fields are used there. Most likley you would have one of more fields there with a yellow exclamation mark, possibly some measures which failed, then go to the measure and check why it failed - e.g. what columns from your query are used, etc. If you find the measure which is causing the issue you can copy it here if you struggle. 

 

 

Also, if you have any original version of the file check if these columns were not used for the relationship, possibly they were but now you can't see this relationship anymore as you removed these columns at some point from the model, so the relationship is broken.

Yes, you are right. If the Query works, the visuals doesn´t. I have loaded the data from the query, but I can´t see the new custom columns I made with the same names?

 

The removed columns are not used in any visuals or ever in the model. This I am sure of. The removed columns where of absolutely no use in this report.

 

There are no exclamation marks on the fields. The fields are using the Case 1RG and Case 2RG, therefor I am sure this has to du with them. For example:

 

FI Surface Deliveries jaettuna luvulla Availability x Average of Working days =
VAR FI_Deliveries_Case1 = CALCULATE([Deliveries], FILTER(D_Item, D_Item[Case1RG] = 1),FILTER(D_Customer,D_Customer[Company] = "FI"))
VAR FI_Deliveries_Case2 = CALCULATE([Deliveries],
FILTER(D_Item, D_Item[Case2RG] = 1),
FILTER(D_Customer,D_Customer[Case2 CustomerNumber] = 1),
FILTER(D_Customer,D_Customer[Company] = "FI"))
VAR FI_Surface_Deliveries_total = FI_Deliveries_Case1+FI_Deliveries_Case2
RETURN
DIVIDE(FI_Surface_Deliveries_total, [Availability x Average of Working days])

so if you make a card with this measure is it displaying (Blank) ? then I would check if there should be any data visible.... e.g. when you filter the tables manually would you get some data? maybe it's just due to your condition there is no data meeting the filters criteria... ?

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors