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.
     

v-yingjl's avatar
v-yingjl
Community 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