Forum Discussion

Netrelemo's avatar
Netrelemo
Helper IV
4 years ago
Solved

How do I get a single value from a function?

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

 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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.

     

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.