Forum Discussion

AtoBI's avatar
AtoBI
Regular Visitor
4 years ago
Solved

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...
Need Help
Show and Tell
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    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.
     

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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's avatar
    AtoBI
    Regular Visitor
    4 years ago

    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's avatar
      AlexisOlson
      Super User
      4 years ago

      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.