none
Can I use a Function as a Source for a query? RRS feed

  • Question

  • I have created the following function in Power Query:

    let LoadInitiation = (site as text) =>
    let
        Source = OData.Feed(site&"/_vti_bin/listdata.svc/Planning"),
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Title"})
    in
        #"Removed Other Columns"
    in LoadInitiation
    

    This Function is based on the following article:

    https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm/

    How can I use this Function as a Source for other queries so that I do not have to call the function over and over?

    Wednesday, October 25, 2017 5:35 PM

Answers

  • To be honest, I have no idea what you are trying to achieve, so here is just some general information about functions.

    You can create a query like:

    let
        Source = LoadInitiation
        ... (other code)
    in
        ... (result)

    But then Source is still a function.

    In general a function will only return its result, when invoked, so at some point you would be likely to use something like MyLoad = Source("mySite").
    So why wouldn't you just call the original function directly: MyLoad = LoadInitiation("mySite")?

    Another example:

    Query MyTextSplit:

    = Text.Split

    You can store the function in a table column and invoke it from there:

    let
        Source = #table(type table[String = text, Delimiter = text],{{"Bla,bla",","},{"Bla bla"," "}}),
        AddedFunction = Table.AddColumn(Source, "FunctionInColumn", each MyTextSplit),
        AddedSplittedText = Table.AddColumn(AddedFunction, "Splitted Text", each [FunctionInColumn]([String],[Delimiter]))
    in
        AddedSplittedText

    If this doesn't answer your question, then please rephrase your question.

    • Marked as answer by HendrikBotha Thursday, October 26, 2017 5:38 AM
    Thursday, October 26, 2017 3:11 AM

All replies

  • To be honest, I have no idea what you are trying to achieve, so here is just some general information about functions.

    You can create a query like:

    let
        Source = LoadInitiation
        ... (other code)
    in
        ... (result)

    But then Source is still a function.

    In general a function will only return its result, when invoked, so at some point you would be likely to use something like MyLoad = Source("mySite").
    So why wouldn't you just call the original function directly: MyLoad = LoadInitiation("mySite")?

    Another example:

    Query MyTextSplit:

    = Text.Split

    You can store the function in a table column and invoke it from there:

    let
        Source = #table(type table[String = text, Delimiter = text],{{"Bla,bla",","},{"Bla bla"," "}}),
        AddedFunction = Table.AddColumn(Source, "FunctionInColumn", each MyTextSplit),
        AddedSplittedText = Table.AddColumn(AddedFunction, "Splitted Text", each [FunctionInColumn]([String],[Delimiter]))
    in
        AddedSplittedText

    If this doesn't answer your question, then please rephrase your question.

    • Marked as answer by HendrikBotha Thursday, October 26, 2017 5:38 AM
    Thursday, October 26, 2017 3:11 AM
  • Hi Marcel

    Thank you for the advice.

    I am trying to read list and folder data from various project sub-sites in a Project web application.

    So as per the link in my original post I created the function to read data from the folders and lists in the sub site by passing the ProjectWorkspaceInternalURL. The problem is I have to call the function over a 100 times as there are many projects. I was hoping to find a solution to reduce this.

    Thursday, October 26, 2017 5:46 AM
  • If you have a table with your ProjectWorkspaceInternalURL'S, you can simply add a custom column with the formula = =LoadInitiation([ProjectWorkspaceInternalURL]), which will give you a column with nested tables that can be expanded.
    Thursday, October 26, 2017 8:25 AM