Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I would like to fetch "red" value based on "objectTypeAttributeId": "1235". Can anyone help me with the expression to fetch ?
JSON response:
"attributes": [
{
"workspaceId": "xxx-xxxx-xxxx-xxxx-xxxxxxxx",
"globalId": "xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx",
"id": "12345678",
"objectTypeAttributeId": "1234",
"objectAttributeValues": [
{
"value": "ABCD-1264",
"searchValue": "ABCD-1264",
"referencedType": false,
"displayValue": "ABCD-1264"
}
],
"objectId": "1264533"
},
{
"workspaceId": "xxx-xxxx-xxxx-xxxx-xxxxxxxx",
"globalId": "xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx",
"id": "12345687",
"objectTypeAttributeId": "1235",
"objectAttributeValues": [
{
"value": "Red",
"searchValue": "Red",
"referencedType": false,
"displayValue": "Red"
}
],
"objectId": "1264533"
},
My query snippet:
#"Added Custom" = Table.AddColumn(#"Expanded Column", "Name", each [attributes]{1}[objectAttributeValues]{0}[value]),
Result : "Red" (but need this value based on objectTypeAttributeId)
1 is hard-coded. I believe this has to be replaced with <"objectTypeAttributeId": "1235"> condition. So that the value will be fetched properly irrespective of the placement in the response.
I am not sure how to do that.
Please let me know if you need more details.
Thank you!
Solved! Go to Solution.
A complete test code, please pay attention to the comments:
let
data = Json.Document("{
""attributes"": [
{
""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"",
""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"",
""id"": ""12345678"",
""objectTypeAttributeId"": ""1234"",
""objectAttributeValues"": [
{
""value"": ""ABCD-1264"",
""searchValue"": ""ABCD-1264"",
""referencedType"": false,
""displayValue"": ""ABCD-1264""
}
],
""objectId"": ""1264533""
},
{
""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"",
""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"",
""id"": ""12345687"",
""objectTypeAttributeId"": ""1235"",
""objectAttributeValues"": [
{
""value"": ""Red"",
""searchValue"": ""Red"",
""referencedType"": false,
""displayValue"": ""Red""
}
],
""objectId"": ""1264533""
}
]
}"),
data2 = Json.Document("{
""attributes"": [
{
""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"",
""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"",
""id"": ""12345678"",
""objectTypeAttributeId"": ""1234"",
""objectAttributeValues"": [
{
""value"": ""ABCD-1264"",
""searchValue"": ""ABCD-1264"",
""referencedType"": false,
""displayValue"": ""ABCD-1264""
}
],
""objectId"": ""1264533""
}
]
}"),
#"Expanded Column" = #table({"Col"}, {{data}, {data2}}),
#"Added Custom" = Table.AddColumn(
#"Expanded Column",
"Name",
// Replace [Col] with the actual column name
each List.Select([Col][attributes], each [objectTypeAttributeId] = "1235"){0}?[objectAttributeValues]?{0}?[searchValue]?
)
in
#"Added Custom"
A complete test code, please pay attention to the comments:
let
data = Json.Document("{
""attributes"": [
{
""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"",
""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"",
""id"": ""12345678"",
""objectTypeAttributeId"": ""1234"",
""objectAttributeValues"": [
{
""value"": ""ABCD-1264"",
""searchValue"": ""ABCD-1264"",
""referencedType"": false,
""displayValue"": ""ABCD-1264""
}
],
""objectId"": ""1264533""
},
{
""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"",
""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"",
""id"": ""12345687"",
""objectTypeAttributeId"": ""1235"",
""objectAttributeValues"": [
{
""value"": ""Red"",
""searchValue"": ""Red"",
""referencedType"": false,
""displayValue"": ""Red""
}
],
""objectId"": ""1264533""
}
]
}"),
data2 = Json.Document("{
""attributes"": [
{
""workspaceId"": ""xxx-xxxx-xxxx-xxxx-xxxxxxxx"",
""globalId"": ""xxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxx"",
""id"": ""12345678"",
""objectTypeAttributeId"": ""1234"",
""objectAttributeValues"": [
{
""value"": ""ABCD-1264"",
""searchValue"": ""ABCD-1264"",
""referencedType"": false,
""displayValue"": ""ABCD-1264""
}
],
""objectId"": ""1264533""
}
]
}"),
#"Expanded Column" = #table({"Col"}, {{data}, {data2}}),
#"Added Custom" = Table.AddColumn(
#"Expanded Column",
"Name",
// Replace [Col] with the actual column name
each List.Select([Col][attributes], each [objectTypeAttributeId] = "1235"){0}?[objectAttributeValues]?{0}?[searchValue]?
)
in
#"Added Custom"
@gsara45 , Try using
#"Added Custom" = Table.AddColumn(#"Expanded Column", "Name", each
let
attributes = [attributes],
filteredAttributes = List.Select(attributes, each _[objectTypeAttributeId] = "1235"),
firstMatch = if List.Count(filteredAttributes) > 0 then filteredAttributes else null,
value = if firstMatch <> null then firstMatch[objectAttributeValues][value] else null
in
value
)
Proud to be a Super User! |
|
Thank you @bhanu_gautam for your resonse.
I am getting below error while using your code.
Expression.Error: We cannot apply field access to the type List.
Details:
Value=[List]
Key=objectAttributeValues
I missed you mention in my previous post that this JSON response has lot attributes like I have mentioned. I just posted for one such instance.
Meaning that there is one [objectTypeAttributeId] = "1235" for the all attributes listed. Just that we not sure about its position within one attribute list.
Here is my full code - hope this will be more clearer
let
url = "https://api.atlassian.com/jsm/assets/workspace/bcc5e987-a7e5-8398-8cc7-ea05j54479e9/v1/object/aql?st...",
body = "{""qlQuery"": ""objectType = Servers""}",
Source = Json.Document(Web.Contents(
url,[
Headers=[#"Authorization"="Basic <emailaddress:API token>",#"Content-Type"="application/json"],
Content=Text.ToBinary(body)
]
)
),
values = Source[values],
#"Converted to Table" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"objectKey", "attributes"}, {"objectKey", "attributes"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column", "Server", each
let
attributes = [attributes],
filteredAttributes = List.Select(attributes, each _[objectTypeAttributeId] = "1235"),
firstMatch = if List.Count(filteredAttributes) > 0 then filteredAttributes else null,
value = if firstMatch <> null then firstMatch[objectAttributeValues][value] else null
in
value)
in
#"Added Custom"
@gsara45 , Use this
let
url = "https://api.atlassian.com/jsm/assets/workspace/bcc5e987-a7e5-8398-8cc7-ea05j54479e9/v1/object/aql?st...",
body = "{""qlQuery"": ""objectType = Servers""}",
Source = Json.Document(Web.Contents(
url, [
Headers = [#"Authorization" = "Basic <emailaddress:API token>", #"Content-Type" = "application/json"],
Content = Text.ToBinary(body)
]
)),
values = Source[values],
#"Converted to Table" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"objectKey", "attributes"}, {"objectKey", "attributes"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column", "Server", each
let
attributes = [attributes],
filteredAttributes = List.Select(attributes, each _[objectTypeAttributeId] = "1235"),
firstMatch = if List.Count(filteredAttributes) > 0 then filteredAttributes else null,
value = if firstMatch <> null then firstMatch[objectAttributeValues][value] else null
in
value
)
in
#"Added Custom"
Proud to be a Super User! |
|
Thanks again!
I am getting the same error.
Moreover, I am not sure what was the change you did in my code. Can you please highlight ?