My client uses O365 (Business) and provided me with Microsoft credentials.
I am able to get source data (csv or Excel file) from applicable SharePoint folder, with the following approach:
Excel – Data – Fron Web – Past URL (after copying it from the "i" icon (i.e. “open details pane”) in the applicable SharePoint folder.
Resulting M code syntax in Source step:
For CSV file:
= Csv.Document(Web.Contents("https://organization name.sharepoint.com/Shared%20Documents/Financial%20Close%20Documents/Argus%20Hub%20Financial%20Config/file name.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None])
For Excel file:
=Excel.Workbook(Web.Contents("https:// organization name.sharepoint.com/Shared%20Documents/Financial%20Close%20Documents/Argus%20Hub%20Financial%20Config/file name.xlsx"), null, true)
As an alternative approach, I’d like to do the following:
In Excel -- Create a defined name (1 cell each) for file path and file name
In Power Query:
Define 1 variable each, for file path and file name; and point to the defined Excel (cell) names per above.
Source step – Concatenate the file path and file name variables; Wrap the same in applicable syntax, depending on whether the file is csv format or Excel.
SharePoint folder path (copied from "i" icon of applicable folder)
https:// organization name.sharepoint.com/Shared%20Documents/Financial%20Close%20Documents/Argus%20Hub%20Financial%20Config
Questions:
1. How should the defined Excel cell name (especially for file path) read, while also taking into account the multiple instances of “%20”
2. How should M Code syntax, in Power Query Advanced Editor ( Source step), read, to correctly accomplish the following?
(a) concatenate file path and file name variables
(b) wrap the concatenated result in “Web.Contents” function and file type i.e. E.g.: Csv.Document or Excel.Workbook
Thank you
You can load a named range in using syntax like:
= Excel.CurrentWorkbook(){[Name="filepath"]}[Content]{0}[Column1]
For the special characters in the file path, you can use something like (assumes Source is the step that loads the name in):
= Text.Combine(List.Transform(Text.Split(Source, "/"), Uri.EscapeDataString), "/")
For the file name you should only need:
= Uri.EscapeDataString(Source)
You can then just use those two query names as variables in your other functions - e.g.
= Csv.Document(Web.Contents(query1 & query2),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None])
