Record.Field(_, projNo) as parameter for a JSON Document Web Contents

[MarkusEng1998]

I use the Json.Document(Web.Contents(....)) to request data. I created a function where I can extract the information for a single project using [projNo]="01"

I have a separate table with the column [projNo]. I want to use the value in this column to extract the information for all of the projects.

Here is my function getProjectURI()

(ProjectNo as text, tblColumn as table)=>
let
	#"FilterColumnTable" = Table.SelectRows(tblColumn, each Record.Field(_,ProjectNo)),
	DatabaseName = "abc",
    ObjectType = "rooms",
    Proj = 
        Json.Document(
            Web.Contents(
                "https://api-us.drofus.com/api/",
                [
                    RelativePath= DatabaseName & "/" & ProjectNo & "/" & ObjectType & "?$select=room_function_0_no,room_function_0_name,room_function_1_no,room_function_1_name,room_func_no,name,description,architect_no,programmed_area,designed_area,rds_status"		
                ]
            )
        )
    in
        Proj

This function works fine as = getProjectURI("01", #"000Projects")

But when I add the function as a step in the Projects table I get the error Expression.Error: The name '_" wasn't recognized.

= getProjectURI(Record.Field(_, [projNo]), Source)

I don't know where to place the Record.Field(_, [projNo]) in the function.

Thank you for any suggestions.

[MarkusEng1998]

Thank you for your patience. I am learning slowly.

[MarkusEng1998]

The correct function is this:

(ProjectNo as text, ObjectType as text, tblColumn as table) =>
let
	#"FilterColumnTable" = Table.SelectRows(tblColumn, each ([ObjectType] = ObjectType)),
	DatabaseName = "abc",
	
    Proj = 
        Json.Document(
            Web.Contents(
                "https://api-us.drofus.com/api/",
                [
                    RelativePath = DatabaseName & "/" & ProjectNo & "/" & ObjectType & "?$select=" & Text.Combine(#"FilterColumnTable"[dRofus.ID], ",")
                ]
            )
        )
    in
        Proj

This function requires two tables. Table1 invokes the function and has the index [projNo]. Table2 is the lookup (mapping) table that stores all of the fields for the API call. Table2 is referenced in the #"filterColumnTable"

[MarkusEng1998 · Accepted solution]

Thank you @latimeria, I figured out my issue. I was calling my function incorrectly!

Instead, I needed to Invoke Custom Function (Add Column tab) to call the function.

This is my solution:

= Table.AddColumn(Source, "getProjectURI", each getProjectURI([projNo], "rooms", #"101mapRooms"))

I had tried to simplify the function but eliminated a key component as you noted:

#"FilterColumnTable"

[latimeria]

Hello @MarkusEng1998 ,

try by replacing _ with r

#"FilterColumnTable" = Table.SelectRows(tblColumn,(r)=> Record.Field(r,ProjectNo)),

[MarkusEng1998]

(ProjectNo as text, tblColumn as table)=>
let
	// #"FilterColumnTable" = Table.SelectRows(tblColumn, each Record.Field(_,ProjectNo)),

    #"FilterColumnTable" = Table.SelectRows(tblColumn, (r) => Record.Field(r,ProjectNo)),
	DatabaseName = "abc",
    ObjectType = "rooms",
	
    Proj = 
        Json.Document(
            Web.Contents(
                "https://api-us.drofus.com/api/",
                [
                    RelativePath= DatabaseName & "/" & ProjectNo & "/" & ObjectType & "?$select=room_function_0_no,room_function_0_name,room_function_1_no,room_function_1_name,room_func_no,name,description,architect_no,programmed_area,designed_area,rds_status"		
                ]
            )
        )
    in
        Proj

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Am I missing the 'each'

[latimeria]

Hi @MarkusEng1998 ,

Sorry, I misunderstood the issue.

Call the function like this: getProjectURI([projNo], Source)
My understanding is that the step #"FilterColumnTable" is useless

#"FilterColumnTable" = Table.SelectRows(tblColumn, (r) => Record.Field(r,ProjectNo)),

you select no row (no condition).

FYI , each is a shortcut for the function (_)=>  .

If it doesn't work, can you provide an example of the whole step when calling the function.