Excel Power Query refresh error

[renigar]

I am trying to fix an Excel file that uses Power Query to import data from another Excel file into Power Pivot. This file was created by a former employee and I am not up on these features of Excel. This file was working fine until this week when it started generating a refresh error.

The Billing Days column exist in the target file but not the source, which hasn't been a problem till now. That column is not used. So how do I tell Power Query to stop looking for that column. I've googled and read all kinds of stuff about it but can't seem to see the answer for looking. I think it should be easy but I don't know. Anybody familiar with this who can point me in the right direction.
Thanks,
renigar

 

[SoftwareRT]

The error you are seeing in Power Query indicates that Power Query is still trying to reference a column called "Billing Days," which no longer exists in the source file. You'll need to update the Power Query to prevent it from looking for that specific column:

Try these steps...​

-- Open Power Query Editor:

• In your Excel file, go to the Data tab.
• Click on Get Data (or Queries & Connections) to open the Power Query Editor.

-- Locate the Query:

• In the Power Query Editor, find the query that is responsible for loading the "Billing Days" column.
• Look for the table or data connection where the error is occurring. The error message mentions "Bill Proof Detail RAW 1", so that's the table or query you need to find.

-- Remove Column Reference:

• Once you have opened the correct query, check the Applied Steps pane on the right-hand side of the Power Query window.
• Look for any steps that reference the "Billing Days" column. The step will likely have a name such as "Removed Columns" or "Renamed Columns."
• Click on the step where the column is being referenced, and either remove that step entirely or edit the step to stop referencing the missing column.

-- Refresh the Query:

• After removing the reference to "Billing Days," click Close & Load to exit the Power Query Editor and reload the data into Excel.
• Try refreshing the data again to see if the error is resolved.

 

[renigar]

Thanks SoftwareRT, this info helped but hasn't solved my problem yet. I I got into the Power Pivot connection properties for Excel Group Billing Raw and found on the Used in tab (includes Billing Days) has the message "Some properties cannot be changed because this connection was modified using the PowerPivot Add-in." So I am currently trying to find out were that modification takes place and change it. I am reading Excel Bible 2019, seeking enlightenment.

The answer I found was with the file that gets refreshed open, click the Power Pivot tab, click Manage, click Diagram View. In the diagram for Bill Proof Detail Raw 1, delete the Billing Days field. There were also two other fields that depended on Billing Days that I deleted. I of course verified with the users of this file that these fields were not needed at all before deleting.

 

[SoftwareRT]

Glad you found the issue -- yes, it can get a bit off track when I was focused on Power Query when it looks like looks like it was Power Pivot.