How do I get a single value from a function?

[Netrelemo]

I thought I could just reduce the custom functions results to the bare minimum, but it doesn't work. 

Expression.Error: We cannot convert a value of type Function to type Text.
Details:
    Value=[Function]
    Type=[Type]

let

    Source = OData.Feed("https://xxxxxxxxx.sharepoint.com/sites/edison/_api/ProjectData", null, [Implementation="2.0"]),
    Projects_table = Source{[Name="Projects",Signature="table"]}[Data],

    #"Removed Other Columns" = Table.SelectColumns(Projects_table,{"ProjectId", "ProjectIdentifier", "ProjectLastPublishedDate", "ProjectModifiedDate", "ProjectName", "ProjectWorkspaceInternalUrl"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"ProjectIdentifier", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ProjectIdentifier] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"ProjectName", Order.Ascending}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Sorted Rows", "getPHRdataFromSite", each getPHRdataFromSite([ProjectWorkspaceInternalUrl]))
in
    #"Invoked Custom Function"

(projectSiteURL as text) as text =>

let
    Source = SharePoint.Tables(projectSiteURL, [Implementation=null, ApiVersion=15]),
    PHRFolder = Source{[Title="ReportArchive"]}[Items],
    #"Removed Other Columns" = Table.SelectColumns(#"PHRFolder",{ "Report Date"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Report Date", Order.Ascending}}),
    #"Kept Last Rows" = Table.LastN(#"Sorted Rows", 1),
    Table2Text = Table.ToList(#"Kept Last Rows")


in 
    getPHRdataFromSite

[v-chenwuz-msft · Accepted solution]

Hi @Netrelemo ,

You can try this code for function:

let getPHRdataFromSite = (projectSiteURL as text) =>
    let
        Source = SharePoint.Tables(projectSiteURL, [Implementation=null, ApiVersion=15]),
        PHRFolder = Source{[Title="ReportArchive"]}[Items],
        #"Removed Other Columns" = Table.SelectColumns(#"PHRFolder",{ "Report Date"}),
        #"Kept Last Rows" = Table.LastN(#"Removed Other Columns", 1)

    in
        #"Kept Last Rows"
in
getPHRdataFromSite

And the table query:

let

    Source = OData.Feed("https://xxxxxxxxx.sharepoint.com/sites/edison/_api/ProjectData", null, [Implementation="2.0"]),
    Projects_table = Source{[Name="Projects",Signature="table"]}[Data],

    #"Removed Other Columns" = Table.SelectColumns(Projects_table,{"ProjectId", "ProjectIdentifier", "ProjectLastPublishedDate", "ProjectModifiedDate", "ProjectName", "ProjectWorkspaceInternalUrl"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"ProjectIdentifier", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ProjectIdentifier] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"ProjectName", Order.Ascending}}),

    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "getPHRdataFromSite", each function([ProjectWorkspaceInternalUrl])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "getPHRdataFromSite", {"Report Date"}, {"getPHRdataFromSite.Report Date"})

in
    #"Expanded Custom"

 Add invoked column and expand them.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

your function returns

getPHRdataFromSite

which is nowhere to be seen in the above code.  But even if you would return Table2Text it would still be a list, not a scalar value.