I was looking to count how many element value pair value we have received in the json response from one of our Web API. Anyone had any luck or can figure out for me how to achieve this. I have included the sample json for your reference and the expected output in power bi, if possible.
We tried to use Record.FieldCount, but that only counted the number of element in the next immediate nested level instead of counting all the element value pair available at the lowest depth.
This is one json with 3 element
(header_acc,accordion_acc2, form_attributes) in the formdata and then it has 20 value in total in those three element. {"schema": {"type": "object", "$schema": "http://json-schema.org/draft-04/schema# …", "required": ["header_acc"], "properties": {"header_acc": {"$ref": "#/definitions/header_acc"}, "accordion_acc2": {"$ref": "#/definitions/accordion_acc2"}}, "definitions": {"header_acc": {"required": ["first_name", "surname", "email_address"], "properties": {"surname": {"type": "string", "pattern": "[a-zA-Z'\\ \\s\\-]*$"}, "first_name": {"type": "string", "pattern": "[a-zA-Z'\\ \\s\\-]*$", "maxLength": 11, "minLength": 1}, "email_address": {"type": "string", "pattern": "(^[a-zA-Z0-9]([a-zA-Z0-9\\_\\-]|\\.[a-zA-Z0-9]){0,29}@[a-zA-Z0-9\\_\\-]{1,30}\\.[a-zA-Z0-9]([a-zA-Z0-9]|\\.[a-zA-Z0-9])+$)"}}}, "accordion_acc2": {"properties": {}}}}, "formdata": {"header_acc": {"surname": "Writing", "first_name": "Will", "email_address": "a.tellman@savethechildren.org.uk", "joinus_update_email": "false"}, "accordion_acc2": {}, "form_attributes": {"hash": "aqe8KTF3s5VWERmHftYYARmsjBrM09BjC9pr+CDFK+Q=", "userNo": "6 5 5 7 0", "channel": "", "form_id": "69", "fund_id": "61", "pageUrl": "https://savethechildren-prod.adobecqms.net/how-you-can-help/leave-a-gift-in-your-will/get-your-free-will-writing-guide …", "form_name": "data-legacy-00019", "fund_code": "Non-financial_0000", "fund_name": "Non-Financial Fund", "campaign_id": "63", "redirectUrl": "/content/stc/gb/en/admin/forms/thankyou.html", "campaign_code": "Will-Writing-Guide-Sign-Up", "campaign_name": "Request a Will Writing Guide", "form_type_name": "Data", "marketing_source": "", "marketing_campaign": ""}}}
This one have 11 element and then 56 element value pair between those 11 element.
{"schema": {"type": "object", "$schema": "http://json-schema.org/draft-04/schema# …", "required": ["donation_amount_acc"], "properties": {"gift_aid_acc": {"$ref": "#/definitions/gift_aid_acc"}, "accordion_endpar": {"$ref": "#/definitions/accordion_endpar"}, "donation_type_acc": {"$ref": "#/definitions/donation_type_acc"}, "review_confirm_acc": {"$ref": "#/definitions/review_confirm_acc"}, "address_billing_acc": {"$ref": "#/definitions/address_billing_acc"}, "donation_amount_acc": {"$ref": "#/definitions/donation_amount_acc"}, "donation_method_acc": {"$ref": "#/definitions/donation_method_acc"}, "donation_prompt_acc": {"$ref": "#/definitions/donation_prompt_acc"}}, "definitions": {"gift_aid_acc": {"properties": {"giftAid": {"radionButtonValue": {"enum": []}}}}, "accordion_endpar": {"properties": {}}, "donation_type_acc": {"properties": {}}, "review_confirm_acc": {"properties": {}}, "address_billing_acc": {"properties": {"joinUs": {"radionButtonValue": {"enum": []}}, "surname": {"type": "string", "pattern": "^[a-zA-Z'\\ \\s\\-]*$"}, "first_name": {"type": "string", "pattern": "^[a-zA-Z'\\ \\s\\-]*$"}, "expiry_date": {"type": "string", "pattern": "(1[0-2]|0[1-9])/[0-9]{2}"}, "account_name": {"type": "string", "pattern": "^[a-zA-Z ,.'-]+$"}, "securityCode": {"type": "string", "pattern": "^[0-9]{3}$"}, "email_address": {"type": "string", "pattern": "^[a-zA-Z0-9]([a-zA-Z0-9\\_\\-]|\\.[a-zA-Z0-9]){0,29}@[a-zA-Z0-9\\_\\-]{1,30}\\.[a-zA-Z0-9]([a-zA-Z0-9]|\\.[a-zA-Z0-9])+$"}, "personal_phone": {"type": "string", "pattern": "(^$)|(^(?:(?:\\(?(?:0(?:0|11)\\)?[\\s-]?\\(?|\\+)44\\)?[\\s-]?(?:\\(?0\\)?[\\s-]?)?)|(?:\\(?0))(?:(?:\\d{5}\\)?[\\s-]?\\d{4,5})|(?:\\d{4}\\)?[\\s-]?(?:\\d{5}|\\d{3}[\\s-]?\\d{3}))|(?:\\d{3}\\)?[\\s-]?\\d{3}[\\s-]?\\d{3,4})|(?:\\d{2}\\)?[\\s-]?\\d{4}[\\s-]?\\d{4}))(?:[\\s-]?(?:x|ext\\.?|\\#)\\d{3,4})?$)"}, "personal_mobile": {"type": "string", "pattern": "(^$)|(^(?:(?:\\(?(?:0(?:0|11)\\)?[\\s-]?\\(?|\\+)44\\)?[\\s-]?(?:\\(?0\\)?[\\s-]?)?)|(?:\\(?0))(?:(?:\\d{5}\\)?[\\s-]?\\d{4,5})|(?:\\d{4}\\)?[\\s-]?(?:\\d{5}|\\d{3}[\\s-]?\\d{3}))|(?:\\d{3}\\)?[\\s-]?\\d{3}[\\s-]?\\d{3,4})|(?:\\d{2}\\)?[\\s-]?\\d{4}[\\s-]?\\d{4}))(?:[\\s-]?(?:x|ext\\.?|\\#)\\d{3,4})?$)"}}}, "donation_amount_acc": {"required": ["currency_amount"], "properties": {"currency_symbol": {"enum": ["€", "£", "$"]}}}, "donation_method_acc": {"properties": {"donation_method_rbg": {"enum": ["paypal", "debit"]}}}, "donation_prompt_acc": {"properties": {"inspired_to_give_rbg": {"radionButtonValue": {"enum": ["Email", "Mail Pack", "Press/News Story", "TV Advert", "Online Advert", "Magazine Leaflet"]}}}}}}, "formdata": {"header_acc": {"above18_cb": "true"}, "gift_aid_acc": {"gift_aid_declaration": "false"}, "transactionId": "0d7ccb45-08df-491c-8109-75b2cf3e6954", "form_attributes": {"hash": "Yhdgl813BgW5QZz/VaHkDJ/5xEgGAba2lDbTCkCh9lQ=", "userNo": "6 5 5 7 0", "channel": "", "form_id": "56", "fund_id": "51", "pageUrl": "https://savethechildren-prod.adobecqms.net/donate/single/donation-single-00005 …", "form_name": "donation-single-00005", "fund_code": "UK-Linked_L010", "fund_name": "Our Work In The UK", "campaign_id": "49", "redirectUrl": "/content/stc/gb/en/admin/forms/thankyou.html", "campaign_code": "UK-Cash", "campaign_name": "UK Single Donation", "form_type_name": "Donation", "marketing_source": "", "marketing_campaign": ""}, "donation_type_acc": {"donation_type_rbg": "own_money"}, "worldpay_response": {"transactionId": "0d7ccb45-08df-491c-8109-75b2cf3e6954", "worldPayToken": "LIVE_SU_0d72d5df-054b-4fec-a1c1-af1787042c1a", "worldPayPaymentStatus": "SUCCESS", "worldPayAuthorizedAmount": 1.0, "worldPayOrderDescription": "Save The Children Donation"}, "review_confirm_acc": {}, "address_billing_acc": {"title": "Mrs", "surname": "DONATION", "mail_town": "London", "billing_cb": "true", "first_name": "TEST", "account_name": "Lim gondal", "billing_town": "London", "mail_country": "GB", "email_address": "xxxx@stc.org.uk", "mail_postcode": "ECxM 4AR", "worldPayToken": "LIVE_SU_0d72d5df-054b-4fec-a1c1-af1787042c1a", "personal_phone": "", "billing_country": "GB", "personal_mobile": "", "billing_postcode": "EC1M 4AR", "joinus_update_sms": "false", "mail_organisation": "Save the Children", "joinus_update_post": "true", "joinus_update_email": "false", "joinus_update_phone": "true", "mail_address_line_1": "1 St. John's Lane", "mail_address_line_2": "", "mail_address_line_3": "", "billing_organisation": "Save the Children", "billing_address_line_1": "1 St. John's Lane", "billing_address_line_2": "", "billing_address_line_3": ""}, "donation_amount_acc": {"currency_code": "USD", "currency_amount": "1", "currency_symbol": "$", "donation_frequency": "single"}, "donation_method_acc": {"donation_method_rbg": "debit"}, "donation_prompt_acc": {}}} So is there a way to show number of element value pair at the lowest level or at any given level. As in the above second example we have transaction id at the depth 1, while by default all our element value pair are at depth 4
