Adding a Column in Data File - Error in Pbix

[Anonymous]

Hello,

My power BI report is getting data from an Excel file with multiple tabs. I need to add a new column to the file, but when I refresh pbix file, I get an error message "column not found". What am I doing wrong?

[v-yiruan-msft]

Hi @Anonymous ,

It seems column be renamed or removed . Please go to Query Editor and view each step applied to / for this column in Query Editor . Check if there is any step applied to rename or remove step to this column . If so , edit the related step in Advance Editor and save it after edited . You can refer to this documentation.

By the way, you mentioned that you need to add a new column to the file. Has the new column been added or not? Will this new column be added to the excel file or the report file?

Best Regards

Rena

[Anonymous]

@v-yiruan-msft 

Rena,

I added a new column to the excel file, opened my pbix file, clicked refresh and got the error message. I have a feeling i am missing something.

thank you,

Ana

[v-yiruan-msft]

Hi @Anonymous,

Whether can refresh successfully before adding this new column? Could you please provide the screenshot of the new column and original columns in related excel sheet? If the data in this sheet can be formatted as a table with hot key(CTRL + T)after the new column be added ? In addition, please provide the scripts of the applied steps for the relevant query in Power Query Editor(select View from the ribbon, then select Advanced Editor).

Best Regards

Rena

[Anonymous]

@v-yiruan-msft 

Dear Rena,

1.  If I revert from the new file (with the column) to the old file, I can refresh successfully. Both source files are different tabs of the same Excel file.

2. Here is the old data from the tab called DATA(2):

2. Here is the new source data from the tab called DATA with the new column "OSD RATE":

3. I was able to highlight all the data in the new file and format it via Ctrl+T

4. Here is the Advanced Query Editor after I open pbix and click Refresh:

5. And here is the regular Query Editor:

Thank you so much!

Ana

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

The error is because your original the "DATA" sheet does not contain Column15 , but it exist in the query "DATA" . So you will get an error when refreshed the report... The original data source of the query "DATA" is from the "DATA" sheet in excel, the new column "OSD_Rate" be added in the sheet "DATA_New", so here you need to update the data source of query “DATA" to " DATA_New " in Advance Editor.

Could you please mark my last post as solution if the problem is solved? Thank you .

Best Regards

Rena

[v-yiruan-msft]

Hi @Anonymous,

As checked your screenshot about Advanced Editor, it seems the original column "TOTAL" be renamed to "ACTUAL", and the column "Column15" be removed. What is about column "Column15"? Could you please provide me your source excel file(exclude sensitive data) and your PBIX file if it is convenient? I will help modify the related PBIX file and re-send to you after modified.

Best Regards

Rena

[Greg_Deckler]

Well, more than likely one of the steps is doing something to a column that has been renamed or is no longer present. You can figure out which step by opening Query Editor and clicking on the Source step. Then the next step down, then the next step until you find the offending step. You may have to use Advanced Editor to correct.