Report refresh issue and column of table not found

[aiyosap]

Hi all,

For some reason when one of my reports was performing refresh automatically, it kept failing and getting the following error message. And as a result, the report failed to refresh.
I have checked the pbix file for the report and refreshed and published it into the workspace manually and no issue.
But when come to automatically refreshing, the report hits the issue below.
Hope you can help. thanks.

[negi007]

@aiyosap did you try below link for help. similar problem was resolved there

Solved: Re: Data Source with Multiple Connections - Microsoft Power BI Community

[aiyosap]

Hi Negi007,

It seems my error has nothing to do with the link you provided as my dataset is not derived from SQL server but it is a csv file provided by other party for us to refresh.

If you look at the error message in the post I sent above (or pasted belw) it is saying "The column "1/01/2021" of the table wasn't found.

Hope you can shade some light.

Regards,

Aiyo

[v-jingzhang]

Hi @aiyosap 

As the error message mentions the column "1/01/2021", can you open the csv file to check whether there is a column named as "1/01/2021" there? At the same time, launch Power Query Editor and open Advanced Editor of the query. Check whether any step refers to a column called "1/01/2021". This "column not found" error usually occurs when a non-existent column is used.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[aiyosap]

Dear Jing,

Thank you on your feedback. Let me explain to you about this strange issue.

• First – I do not face this issue when I manually refresh the dashboard via Power BI Desktop and later publish into the workspace for business to consume the report. This problem (i.e., The column '1/01/2021' of the table wasn't found) ONLY exists when it is auto scheduled refreshed
• Second – There is no problem on the auto scheduled refresh for the past two months and the problem stated only took place last week.

Please refer to power query screenshot for the column "1/01/2021" below 

The following is the querry error screenshots.

the following are the power query codes copied from editor:

let
Source = MgmtFees,
#"Detected Type Mismatches" = let
tableWithOnlyPrimitiveTypes = Table.SelectColumns(Source, Table.ColumnsOfType(Source, {type nullable number, type nullable text, type nullable logical, type nullable date, type nullable datetime, type nullable datetimezone, type nullable time, type nullable duration})),
recordTypeFields = Type.RecordFields(Type.TableRow(Value.Type(tableWithOnlyPrimitiveTypes))),
fieldNames = Record.FieldNames(recordTypeFields),
fieldTypes = List.Transform(Record.ToList(recordTypeFields), each [Type]),
pairs = List.Transform(List.Positions(fieldNames), (i) => {fieldNames{i}, (v) => if v = null or Value.Is(v, fieldTypes{i}) then v else error [Message = "The type of the value does not match the type of the column.", Detail = v], fieldTypes{i}})
in
Table.TransformColumns(Source, pairs),
#"Added Index" = Table.AddIndexColumn(#"Detected Type Mismatches", "Row Number" ,1),
#"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"File Date", "Customer", "Billed Date", "Order ID", "Case ID", "Team Name", "Fund Name", "Policy Number", "Claim Number", "Participant State", "Order Date", "Completion Date", "Source System", "Insurance Type", "Cycle Time", "On Hold Days", "Product Name", "Order Requester", "Provider Person", "New Service Charge (Ex GST)", "Service Charge (Inc GST)", "Department", "Status", "Product Segment", "Customer/Department", "Cancellation Reason", "Invoice #", "Charge Added Date", "Billing Group", "Charge Ref", "Volume", "Product Segment_1", "Fund Name + Department", "Fund Name 1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "File Date", "Customer", "Billed Date", "Order ID", "Case ID", "Team Name", "Fund Name", "Policy Number", "Claim Number", "Participant State", "Order Date", "Completion Date", "Source System", "Insurance Type", "Cycle Time", "On Hold Days", "Product Name", "Order Requester", "Provider Person", "New Service Charge (Ex GST)", "Service Charge (Inc GST)", "Department", "Status", "Product Segment", "Customer/Department", "Cancellation Reason", "Invoice #", "Charge Added Date", "Billing Group", "Charge Ref", "Volume", "Product Segment_1", "Fund Name + Department", "Fund Name 1"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Reordered Columns", {{"Charge Added Date", null}})
in
#"Replaced Errors"

thanks Jingg

Regards

Aiyo