Datamart SharePoint Folder Excel files not automatically loading

[willclein]

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 [binary] for a file in the SharePoint folder.

In Datamart PQ, when I click the [binary] field, it does the Navigation step without importing the workbook and shows the Excel icon and information about the file. Typically when this happens in PQ, I can double-click the Excel icon and it will load the workbook or there is an option in Binary Tools to Open As and Excel document.

Neither of these options are available in Datamart PQ, but it is obviously capable of the same process since I can go into Advanced Editor and manually load the workbook using Excel.Workbook(Navigation). Is this intended behavior (including currently being developed for the tool) or is this a bug?

[v-xiaoyan-msft]

Hi @willclein ,

This is a new feature, would you mind providing a few screenshots about your issue? (Please erase sensitive data.) We will try to collect feedback from other users to see if similar experiences occur with you. If it goes well, we will give you feedback next Monday.

Best Regards,
Community Support Team _ Caitlyn

[hmj3b5]

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:

[v-xiaoyan-msft]

Hi @willclein ,

As this is a new feature currently in Preview, we have received feedback from the PG team that binary content is not supported at this time. We will log it as a bug and fix it later on, but for now please CX to load and extend the SPO file in one query.

Best Regards,
Community Support Team _ Caitlyn