Forum Discussion
Source syntax in a custom function
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
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 SourceBest 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.
5 Replies
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.
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
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.
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 SourceBest 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.
