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 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

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.
     

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper 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
        Icon for Super User rankSuper 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.

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    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.
     

    • AtoBI's avatar
      AtoBI
      Regular Visitor
      4 years ago

      Hi v-yingjl 

       

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