Column does not exist in dataset when data source (Excel) has unused columns removed

[kyleb350]

I have a report which uses a SharePoint Excel sheet to provide information on incoming shipments. I have users save new versions of the file over the current version for ease of use, but the issue seems to arise as they add and remove columns with daily comments. 

I've double checked in the Power Query Advanced Editor that my query for this does not reference any of these columns; however, on my scheduled refresh, it errors out stating it cannot find the daily comment column that has been deleted. Despite no reference of this, Power BI still seems to keep track of it. I then open the file in desktop, do a local refresh, see no error there, and republish.

Is there any way around this without having to republish each time?

[v-yanjiang-msft]

Hi @kyleb350 ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft · Accepted solution]

Hi @kyleb350 ,

According to your description, you have removed the unused columns, this is also "referencing" them.

If you don't want to republish the report, you can choose "Remove Other Columns".

For instance the below table, if you want to remove the Column4, don't directly remove it, but select Column1, Column2, Column3 at the same time first and then click Remove Other Columns.

Here's a blog for your reference:  Refresh error: The xxxx column does not exist in t... - Microsoft Power BI Community

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kyleb350]

Thanks! Sorry for the delay. Using the "Remove other columns" step worked. I didn't see that feature hiding there 🙂

[edhans]

I'd need to see the M code, but if you are deleting columns this can be a problem. I almost always select the columns I want. Those are explictly selected, while others are ignored. There are other parameters in the connection that could be affecting it too. So let us see some code.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[kyleb350]

Thanks @edhans . As you can see below, I am dealing with strictly with columns that are NOT removed. I simply do some cleanup of the data. Columns that do come and go are "rolling" comments. With each new version, and new daily comment is added, and the one from two weeks ago is removed. The file is then pasted into a SharePoint location overwriting the old version ready for when the BI dataset refreshes. I suspect the solution may be more around a process change vs a code change, but just want to check.

let Source = Excel.Workbook(Web.Contents("Sheet.xlsx"), null, true),

Company_Sheet = Source{[Item="Company",Kind="Sheet"]}[Data],

#"Promoted Headers" = Table.PromoteHeaders(Company_Sheet, [PromoteAllScalars=true]),

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"BOOKING #", type any}, {"SHIPPER", type text}, {"File#", type text}, {"PO#", type text}, {"customer", type text}, {"QP#", type any}, {"# OF CONTAINERS", type any}, {"CONTAINER SIZE ", type text}, {"CNTR#", type text}, {"CARTON QTY", type any}, {"CBM", type any}, {"READY DATE", type date}, {"ORIGIN", type text}, {"Port", type text}, {"DESTINATION", type text}, {"FINAL DESTINATION NAME#(lf)", type text}, {"ETD", type date}, {"ETA TO PORT", type date}, {"ETA FINAL DESTINATION#(lf)", type text}}),

#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"PO#", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "PO#"),

#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"PO#", type text}}),

#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"PO#", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "PO#"),

#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"PO#", type text}}), #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"PO#", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "PO#"),

#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"PO#", type text}}),

#"Filtered Rows" = Table.SelectRows(#"Changed Type3", each ([#"PO#"] <> null and [#"PO#"] <> "" and [#"PO#"] <> "1" and [#"PO#"] <> "1-A" and [#"PO#"] <> "1-B" and [#"PO#"] <> "2" and [#"PO#"] <> "2773" and [#"PO#"] <> "3" and [#"PO#"] <> "4" and [#"PO#"] <> "A" and [#"PO#"] <> "B" and [#"PO#"] <> "C" and [#"PO#"] <> "PART" and [#"PO#"] <> "part" and [#"PO#"] <> "PO")),

#"Added Prefix" = Table.TransformColumns(#"Filtered Rows", {{"PO#", each "00" & _, type text}}),

#"Extracted Text After Delimiter" = Table.TransformColumns(#"Added Prefix", {{"CNTR#", each Text.AfterDelimiter(_, "#"), type text}})

in

#"Extracted Text After Delimiter"