I've started using Datamarts, and I've noticed an issue when connecting to Excel files stored in a SharePoint folder. Typically, PQ will detect and load the Excel workbook when I click into the [bina...
I have similar issue on loading Excel files from a Sharpoint folder...
Editor statement:
let
Source = SharePoint.Files("https://adm.sharepoint.com/sites/CapExPriorization-Override", [ApiVersion = 15]),
#"Filtered rows" = Table.SelectRows(Source, each ([Name] = "Animal Nutrition Prioritization Working File.xlsx")),
#"Filtered hidden files" = Table.SelectRows(#"Filtered rows", each [Hidden] <> true),
#"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file", each #"Transform file"([Content])),
#"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),
#"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file"}),
#"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", Table.ColumnNames(#"Transform file"(#"Sample file"))),
#"Changed column type" = Table.TransformColumnTypes(#"Expanded table column", {{"Source.Name", type text}, {"Priority FY22", type text}, {"Change", type text}, {"Wave ID (Enter #)", Int64.Type}, {"Project Name (Lookup)", type text}, {"CAPEX Portfolio Year (Lookup)", type text}, {"2022 Capex ($K) (Lookup)", type number}, {"Wave inferred Priority 22", type number}, {"Planning Notes", type text}}),
#"Removed columns" = Table.RemoveColumns(#"Changed column type", {"Project Name (Lookup)", "CAPEX Portfolio Year (Lookup)", "2022 Capex ($K) (Lookup)"}),
#"Removed errors" = Table.RemoveRowsWithErrors(#"Removed columns")
in
#"Removed errors"
Loads but when I go to save and it analyses the table I get this error: