Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MarkusEng1998
Resolver II
Resolver II

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"

MarkusEng1998_0-1707162951406.png

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.

1 ACCEPTED SOLUTION
MarkusEng1998
Resolver II
Resolver II

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"

 

View solution in original post

6 REPLIES 6
MarkusEng1998
Resolver II
Resolver II

Thank you for your patience. I am learning slowly.

MarkusEng1998
Resolver II
Resolver II

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
Resolver II
Resolver II

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
Solution Specialist
Solution Specialist

Hello @MarkusEng1998 ,

 

try by replacing _ with r

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

 

(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'

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors