Query to Custom Function Guidance

scott_od

New member
Joined
Mar 15, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
O365
Hi, I am looking for some help with converting my query to a custom function, in order to retrieve all files in a folder, but I am unsure which part of the code should be replaced with the path&name variable.

custfunc.JPG

code attached ^^

thanks in advance for anybody who can help me out :)
 
Code:
let GetFiles=(Path,Name) =>

  
let
      
    Source = SharePoint.Files("https://xxxyyy.sharepioint.com/teamname", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://xxxyyy.sharepioint.com/teamname/library/folder/")),
    #"excelfile xlsx_https://https://xxxyyy.sharepioint.com/teamname/library/folder/" = #"Filtered Rows"{[Name="excelfile.xlsx",#"Folder Path"="https://xxxyyy.sharepioint.com/teamname/library/folder/"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"excelfile xlsx_https://xxxyyy.sharepioint.com/teamname/library/folder/"),
    #"exceldocument_Sheet" = #"Imported Excel"{[Item="worksheetname",Kind="Sheet"]}[Data],
    GetDate = Record.Field(#"worksheetname"{8},"Column5"),
    GetName = Record.Field(#"worksheetname"{6},"Column3"),
    GetNumber = Record.Field(#"worksheetname"{7},"Column3"),
    Custom1 = #"worksheetname",
    #"Replaced blanks" = Table.ReplaceValue(Custom1,"",null,Replacer.ReplaceValue,{"Column2"}),
    #"Filtered null" = Table.SelectRows(#"Replaced blanks", each ([Column2] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered null", [PromoteAllScalars=true]),
    #"Filtered duplicate headers" = Table.SelectRows(#"Promoted Headers", each ([Reference Number] <> "Reference Number")),
    #"Renamed Size" = Table.RenameColumns(#"Filtered duplicate headers",{{"Size", "20'"}}),
    #"Replaced nulls" = Table.ReplaceValue(#"Renamed Size",null,0,Replacer.ReplaceValue,{"20'", "40'"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced container nulls",{"Outbound Reference", "Reference", "Gross Weight", "Weight MT", "20'", "40'"}),
    #"Add Date" = Table.AddColumn(#"Removed Other Columns", "Date", each GetDate),
    #"Add Name" = Table.AddColumn(#"Add Date", "Name", each GetName),
    #"Add Number" = Table.AddColumn(#"Add Name", "Number", each GetNumber)
in
    #"Add Voyage Number"


in GetFiles
 
Back
Top