Results 1 to 5 of 5

Thread: Query to Custom Function Guidance

  1. #1
    Neophyte scott_od's Avatar
    Join Date
    Mar 2021
    Posts
    3
    Articles
    0
    Excel Version
    O365

    Query to Custom Function Guidance



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    Click image for larger version. 

Name:	custfunc.JPG 
Views:	12 
Size:	68.0 KB 
ID:	10645

    code attached ^^

    thanks in advance for anybody who can help me out

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,708
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    Welcome to the forum!

    Please post the code, not a picture of it.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte scott_od's Avatar
    Join Date
    Mar 2021
    Posts
    3
    Articles
    0
    Excel Version
    O365
    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

  4. #4
    Neophyte scott_od's Avatar
    Join Date
    Mar 2021
    Posts
    3
    Articles
    0
    Excel Version
    O365
    In case it is helpful to others, I eventually found the solution to my question in this article:
    https://exceleratorbi.com.au/consoli...ng-powerquery/

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,708
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    Thanks for stopping by to share this - very much appreciated.
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •