My end goal is to pull 2 data points from internal company url and display it in excel.

end goal

Typically, there are 2 types of “Data_Response_Code” – 200 and 404.
My idea is to first use Column A2 (id) to check if “Data_Flow”>”Data_Response_Code” = 200. If it is, then proceed to get the data from “Instrument_Common”>”Instrument_Full_Name” and “Instrument_Common”>”CFI_Code” to display in Column B2 and C2 respectively. Then proceed to check for the next row.
However, if Column A2 (id) returns “Data_Flow”>”Data_Response_Code” = 404, then Column B2 and C2 should show blank.

——404——–

{
    "Instrument": [
        {
            "Data_Flow": {
                "Data_Last_Updated_Date_Time": null,
                "Data_Response_Code": 404
            },
            "Instrument_Common": null,
            "Entity": null,
            "Cash_Financial_Instrument": null
        }
    ]
}

——200——–

{
    "Instrument": [
        {
            "Data_Flow": {
                "Data_Last_Updated_Date_Time": null,
                "Data_Response_Code": 200
            },
            "Instrument_Common": {
                "Instrument_Full_Name": "HSBC 2023",
                "ISIN": "XS2862978116",
                "CFI_Code": "XXXX4",
            },
            "Entity": null,
            "Cash_Financial_Instrument": null

            
        }
    ]
}

I have created a function. which eventually i’ll have another table which contains the Column A (Id), and i’ll invoke this function to get my Column B and C data. Below this is the Advanced Editor.

(id as text)=>
let
    Source = Json.Document(Web.Contents("https://mrsrdac.gdc.standardchartered.com:7778/Instrument?RequestType=ByIdentifier&IdentifierType=ISIN&IdentifierValue="&id&"&Requester=GPTM")),
    Instrument = Source[Instrument],
    Instrument1 = Instrument{0},
    #"Converted to Table" = Record.ToTable(Instrument1),
    Value = #"Converted to Table"{1}[Value],
    #"Converted to Table1" = Record.ToTable(Value),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table1", each ([Name] = "CFI_Code" or [Name] = "Instrument_Full_Name")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Name]), "Name", "Value")
in
    #"Pivoted Column"

if “Data_Flow”>”Data_Response_Code” = 200, all is good. I’m able to get my data. However, if it’s 404, then I get an error which I do not know how to bypass. Please help!

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value null to type Record.
Details:
    Value=
    Type=[Type]