Report failed to refresh but everything are behaving perfect when refresh down in manual

[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.

Underlying error code: -2147467259
Underlying error message: The column '1/01/2021' of the table wasn't found.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.Detail: 1/01/2021
Microsoft.Data.Mashup.ValueError.Reason: Expression.Error
Cluster URI: WABI-AUSTRALIA-SOUTHEAST-redirect.analysis.windows.net
Activity ID: 99aeb56f-c572-48a1-88f1-2f1831d90864
Request ID: 59c19974-a623-3b47-4ae6-b03bdf8e8ec7
Time: 2022-08-17 01:06:04Z

Regards,

Aiyosap

[lbendlin]

show a sanitized version of your Power Query code.

[aiyosap]

Hi Lbendin,

In addition to the query's power query code I provided minutes ago, the following are the code for the query error in the power BI of the report -

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"}),
#"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"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Reordered Columns", {{"Charge Added Date", null}})
in
#"Replaced Errors"

[lbendlin]

Your source text files are in a wide format (with columns being added/removed).  That is not something Power BI likes.  Convert them into long format by unpivoting them.  That will also take care of the errors you see.

Provide a sample text file or paste a couple rows here so we can show you how to unpivot them.

[aiyosap]

Hi Ibendlin,

please refer to the power query code. Thanks.

let
Source = Folder.Files("\\TTT\TTT\TTTS\TTT\Technology\TTT\TTT\TTTFiles\Invoices"),
#"Lowercased Text" = Table.TransformColumns(Source,{{"Extension", Text.Lower, type text}}),
#"Filtered Rows" = Table.SelectRows(#"Lowercased Text", each [Extension] = ".csv"),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Name", "Content"}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",".csv","",Replacer.ReplaceText,{"Name"}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each Text.Contains([Name], "MgmtFees")),
#"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows1","UHGInvoice_MgmtFees_","",Replacer.ReplaceText,{"Name"}),
#"Extracted First Characters" = Table.TransformColumns(#"Replaced Value1", {{"Name", each Text.Start(_, 6), type text}}),
#"Added Suffix" = Table.TransformColumns(#"Extracted First Characters", {{"Name", each _ & "01", type text}}),
#"Added Custom" = Table.AddColumn(#"Added Suffix", "Custom", each Csv.Document([Content])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Name", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Name", "File Date"}}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Renamed Columns", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"1/01/2021", type date}, {"Customer", type text}, {"Billed Date", type date}, {"Order ID", type text}, {"Case ID", type text}, {"Team Name", type text}, {"Fund Name", type text}, {"Policy Number", type text}, {"Claim Number", type text}, {"Participant State", type text}, {"Order Date", type date}, {"Completion Date", type date}, {"Source System", type any}, {"Insurance Type", type text}, {"Cycle Time", Int64.Type}, {"Holding Days", Int64.Type}, {"Product Name", type text}, {"Order Requester", type text}, {"Provider Person", type text}, {"New Service Charge (Ex GST)", type number}, {"Service Charge (Inc GST)", type number}, {"Department", type text}, {"Status", type text}, {"Product Segment", type text}, {"Customer/Department", type text}, {"Cancellation Reason", type any}, {"Invoice #", type text}, {"Charge Added Date", type any}, {"Billing Group", type any}, {"Charge Ref", type any}, {"Volume", type any}, {"Product Segment_1", type any}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"1/01/2021", "File Date"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Source System", type text}, {"Charge Added Date", type date}, {"Billing Group", type text}, {"Charge Ref", type text}, {"Volume", type text}, {"Product Segment_1", type text}, {"Cancellation Reason", type text}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type2", each ([Customer] <> "Customer")),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows2", "Fund Name + Department", each [Fund Name]&""&[Department]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Fund Name + Department", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type3", {"Fund Name + Department"}, Fund, {"Fund Name + Department"}, "Fund", JoinKind.LeftOuter),
#"Expanded Fund" = Table.ExpandTableColumn(#"Merged Queries", "Fund", {"Department 1", "Fund Name 1"}, {"Department 1", "Fund Name 1"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded Fund", {{"Charge Added Date", null}}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Errors", "Business", each if Text.Contains([Department], "New Business") then "New Business" else "Claims Business"),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Business", type text}})
in
#"Changed Type4"