Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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"

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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"

  • latimeria's avatar
    latimeria
    Solution Specialist
    2 years ago

    Hello Anonymous ,

     

    try by replacing _ with r

    #"FilterColumnTable" = Table.SelectRows(tblColumn,(r)=> Record.Field(r,ProjectNo)),
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

       

      (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's avatar
        latimeria
        Solution Specialist
        2 years ago

        Hi Anonymous ,

         

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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"

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you for your patience. I am learning slowly.