How to format json body to an external REST API

[Anonymous]

Hi Everyone,

I am a newbie in PowerBI and hope I can get some help here. I need to call an REST API (hosted on AWS Cloud) to apply scrambling on a table column credit card number(CC). My table in Power BI looks like this

My AWS Cloud API only support POST method and I have tested it to be working locally on my laptop via Postman. The API will need the body in json and it will output  json  as well

The input string will be something like  (note that the "data" value looks like a list object but it really is only a string literal and I will need to parameterize this using the CC table column)

{"user": "leel", "data_element": "deTokCCN", "data": ["1234567890123456"]}

What I need to accomplish is to loop through the table CC column and call this Rest API to output the scrambled output instead of the real CC number. I have put together a draft script through googling which looks like this:

let
  url = "https://y0suavjxxx.execute-api.ap-southeast-1.amazonaws.com/foongping_stage/v1/protect",
  body = "{""user"": ""leel"", ""data_element"": ""deTokCCN"", ""data"": [""1234567890123456""]}",
  header = [ #"Authorization"="AWS AWSAccessKeyId=XXXXXXXX",
                         #"Content-Type"= "application/json"],
  Source = Json.Document(Web.Contents(url,[Headers=header,Content=Text.ToBinary(body)]))
in
  Source

Now, I have a couple of questions here:

1. If I need to subtitute the hardcoded CC number in the above script, should the body line look like this: 

       body = "{""user"": ""leel"", ""data_element"": ""deTokCCN"", ""data"": ["[CC]"]}"

     such that my API will get exactly this --> {"user": "leel", "data_element": "deTokCCN", "data": ["1234567890123456"]}

2. There are no syntax error in the Advance Editor with the above block of code but I am getting this error "Expression.Error: Access to the resource is forbidden". Can you help me understand what's wrong here? Has this something to do with my 'Authorization" header? I specified this "AWS AWSAccessKeyId=XXXXXXXX" based on AWS documentation

3. The returned results if I am successful in calling the API will be a json (refer to Postman screen), how will I be able to parse the JSON to extract only the scambled CC number ?

Thanks,

FoongPing

[Anonymous]

Hi, thanks for your reply, I have used an actual key, 'XXXXXX' was because I did not want to reveal the actual key here for security reason.

[Gabriel_Walkman]

Not familiar with API requests in Power BI, but I'd imagine you need an actual access key instead of XXXXXXXX?