Source syntax in a custom function

[AtoBI]

Hello,

I would like to consolidate multiple files in one spreadsheet, so I created a sample file and from this a function. Then I invoke this function to the other files, so far so good. The problem is that my path of my folder was local, once I move it to OneDrive or SharePoint the path becomes an url and I am not sure why I cannot replicate the same steps.

I'll add screenshot to explain because I need help with the syntax

Step 1 - Local PathStep 2 - Local PathStep 1 - OneDrive locationStep 2 - OneDrive locationStep 3 - OneDrive location

[v-yingjl · Accepted solution]

Hi @AtoBI ,

You can define a custom function like this to use OneDriveURL, OneDrivceFolderName and FileName as parameters to get your files from OneDrive:

let
    Source = (OneDriveURL,OneDriveFolder, FileName) =>
    let
        Source = SharePoint.Contents(OneDriveURL),
        Documents = Source{[Name="Documents"]}[Content],
        Folder = Documents{[Name= OneDriveFolder]}[Content],
        #"FileName" = Folder{[Name= FileName]}[Content],
        #"Imported CSV" = Csv.Document(#"FileName",2,"",ExtraValues.Ignore,1252),
        #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Value", Int64.Type}})
    in
        #"Changed Type"
in
    Source

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
 

[AtoBI]

Hi @v-yingjl 

Will give it a try and will let you know, thanks for your help and for your time

[AlexisOlson]

If you just want the first file in your OnDrive folder, then the filename is irrelevant and you can write

Source = Csv.Document(OneDriveFolder{0}[Content])

The {0} indicates the first (index = 0) row.

[AtoBI]

Hi Alexis

Thanks for your reply

However am not sure it answers my question, I do want to use the variable filename to be able to apply all the transformations I made to the sample files to the others file of my OneDrive folder. So the source in my function should be dynamic

Hope it makes more sense

[AlexisOlson]

So you don't want the first file in your OnDrive folder then...

How about instead of index 0, you use the filename like this?

Source = Csv.Document(OneDriveFolder{[Name=filename]}[Content])

The syntax "{[Name=filename]}" refers to the row index where the Name column matches your filename variable.