Thank you for your reply BA.Pete.
The query pulls yearly financial data from a folder full of excel documents using a custom function it seems. Below is the function (step) that is used to pull in this data. All of the preceding steps are getting to the folder location and removing unncessary files through transformations. After this step it is normal transformations cleaning up the data. Nothing appears wrong within the transformations, however when/if I try and 'load more' than the sample (1000) rows then the error data.format error '#REF!' appears. The problem is that the query does not tell me where the error is stemming from, which of the myriad of excel files that the query pulls from within the folder.
(FileName as text, FolderPath as text)=>
let
Source = SharePoint.Files(
),
#"Selected File" = Source{
[Name=FileName,
#"Folder Path"=FolderPath
]}[Content],
#"Imported Excel" = Excel.Workbook(
#"Selected File"
),
#"Selected Range" = #"Imported Excel"{
[Item="R_RollupData",Kind="DefinedName"]
}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Selected Range", [PromoteAllScalars = true]),
#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers", {
"F19-01 Hours", "F19-02 Hours", "F19-03 Hours", "F19-04 Hours", "F19-05 Hours", "F19-06 Hours", "F19-07 Hours", "F19-08 Hours", "F19-09 Hours", "F19-10 Hours", "F19-11 Hours", "F19-12 Hours",
"F20-01 Hours", "F20-02 Hours", "F20-03 Hours", "F20-04 Hours", "F20-05 Hours", "F20-06 Hours", "F20-07 Hours", "F20-08 Hours", "F20-09 Hours", "F20-10 Hours", "F20-11 Hours", "F20-12 Hours",
"F21-01 Hours", "F21-02 Hours", "F21-03 Hours", "F21-04 Hours", "F21-05 Hours", "F21-06 Hours", "F21-07 Hours", "F21-08 Hours", "F21-09 Hours", "F21-10 Hours", "F21-11 Hours", "F21-12 Hours",
"F22-01 Hours", "F22-02 Hours", "F22-03 Hours", "F22-04 Hours", "F22-05 Hours", "F22-06 Hours", "F22-07 Hours", "F22-08 Hours", "F22-09 Hours", "F22-10 Hours", "F22-11 Hours", "F22-12 Hours",
"F23-01 Hours", "F23-02 Hours", "F23-03 Hours", "F23-04 Hours", "F23-05 Hours", "F23-06 Hours", "F23-07 Hours", "F23-08 Hours", "F23-09 Hours", "F23-10 Hours", "F23-11 Hours", "F23-12 Hours",
"F24-01 Hours", "F24-02 Hours", "F24-03 Hours", "F24-04 Hours", "F24-05 Hours", "F24-06 Hours", "F24-07 Hours", "F24-08 Hours", "F24-09 Hours", "F24-10 Hours", "F24-11 Hours", "F24-12 Hours",
"F25-01 Hours", "F25-02 Hours", "F25-03 Hours", "F25-04 Hours", "F25-05 Hours", "F25-06 Hours", "F25-07 Hours", "F25-08 Hours", "F25-09 Hours", "F25-10 Hours", "F25-11 Hours", "F25-12 Hours",
"Team", "Role", "Name", "Rate", "Specialty", "Timesheet Activity"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Project Number", "Spend Type", "Source", "Vendor Name", "Quote Number", "Description", "Spend Class", "Spend Category", "Table Row", "File Template Version", "File Last Refresh CT"}, "FYY-FP", "Cost"),
#"Filtered Rows - Cost <> 0" = Table.SelectRows(#"Unpivoted Columns", each [Cost] <> 0 and [Cost] <> null and [Cost] <> "0" and [Cost] <> ""),
#"Added Column - Fiscal Year" = Table.AddColumn(#"Filtered Rows - Cost <> 0", "Fiscal Year", each Number.From(
"20" & Text.Middle(
[#"FYY-FP"],1,2)
)),
#"Added Column - Fiscal Period" = Table.AddColumn(#"Added Column - Fiscal Year", "Fiscal Period", each Number.From(Text.End([#"FYY-FP"], 2))),
#"Renamed Columns" = Table.RenameColumns(#"Added Column - Fiscal Period", {{"Cost", "Amount"}, {"Spend Type", "Transaction Type"}, {"Project Number", "Project Number Short"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns", {"FYY-FP"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1", {{"Project Number Short", Int64.Type}, {"Fiscal Year", Int64.Type}, {"Fiscal Period", Int64.Type}, {"Table Row", Int64.Type}, {"Amount", Currency.Type}, {"File Last Refresh CT", type datetime}, {"Spend Category", type text}, {"Spend Class", type text}, {"Description", type text}, {"Quote Number", type text}, {"Vendor Name", type text}, {"Source", type text}, {"Transaction Type", type text}})
in
#"Changed Type"
