We cannot convert the values to type Record. from cosmos to pbi

[mtembhurne]

Hi,

I am trying to load data from Azure Cosmos to Power BI but getting error for loading one column "Validation Status".
It says  We cannot convert the value "approved" and "declined" to type Record.

The  Data source have records in below format

"responsePayload": "{\"success\":true,\"message\":\"validate successfully\",\"statusCode\":000,\"validationStatus\":\"approved\"}

I have tried changing type of validation Sattus to (Text and Any both) in query editor using
Changed Type" = Table.TransformColumnTypes(#"Expanded Document.auditData.responsePayload",{
{"Document.auditData.responsePayload.validationStatus", type text},
but still no use.

Can anyone please help me how to get rid of this error.

Thank You.

[Anonymous]

Hi @mtembhurne ,

Have you solved your problem?
I'm sorry that this kind of question only provides error information without complete or relevant part of the M code, so it's hard for me to know where your problem is. Could you provide the relevant M code?

Best Regards,
Dino Tao

[mtembhurne]

Hi, 
I am loading data into Power BI from Azure Cosmos. My M Code is 

let

    Source = DocumentDB.Contents("https://....../"),

    #"payments-audit-trail1" = Source{[id="payments-audit-trail"]}[Collections],

    #"payments-audit-trail_payments-audit-trail" = #"payments-audit-trail1"{[db_id="payments-audit-trail",id="payments-audit-trail"]}[Documents],

    #"Expanded Document" = Table.ExpandRecordColumn(#"payments-audit-trail_payments-audit-trail", "Document", {"createdDate", "auditData"}, {"Document.createdDate", "Document.auditData"}),

    #"Expanded Document.auditData" = Table.ExpandRecordColumn(#"Expanded Document", "Document.auditData", {"eventName", "eventDescription", "requestPayload"}, {"Document.auditData.eventName", "Document.auditData.eventDescription", "Document.auditData.requestPayload"}),

    #"Filtered Rows" = Table.SelectRows(#"Expanded Document.auditData", each ([Document.auditData.eventName] = "CVS_VALIDATE_ACCERTIFY") and ([Document.auditData.eventDescription] = "Exit : CVS call to Accertify")),

    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Document.createdDate", type datetime}}),

    #"Expanded Document.auditData.requestPayload" = Table.ExpandRecordColumn(#"Changed Type", "Document.auditData.requestPayload", {"transaction"}, {"Document.auditData.requestPayload.transaction"}),

    #"Filtered Rows1" = Table.SelectRows(#"Expanded Document.auditData.requestPayload", each Date.IsInCurrentMonth([Document.createdDate]))

in

    #"Filtered Rows1"

I am not able to expand/Parse the RequestPalyload in Power BI. 

In Source format of Requestpayload is like string 

 "requestPayload": "{\"transaction\":{\"transactionId\":\"3d528dbe-d655-4e8a-9661-a491e314e7f4\",\"inauthTransactionId\":\"e9a6c1915b2b34c06821ac27ed78cdde2955\",\"sessionId\":\"5ec1e7a6-99f8-48dc-846f-e4aa19944162\",\"confirmationNumbers\":\"PENDING\",\"bookingType\":\"GLOBAL\",\"paymentFailuresCount\":0,\"businessFailuresCount\":0,\"transactionType\":\"Call-Center\",\"transactionDateTime\":\"2024-08-13T16:12:52-0400\",\"transactionTotalAmount\":178.92,\"salesChannel\":null,\"ipAddress\":\"127.0.0.1\",\"processorResponseText\":null,\"orderStatus\":null,\"guest\":{\"loggedIn\":\"No\",\"memberId\":\"\",\"firstName\":\"K***m\",\"lastName\":\"**\",\"emailAddress\":\"k****************m\",\"phone\":\"(************7\",\"createdDate\":null,\"lastModifiedDate\":null,\"address1\":\"8********s\",\"address2\":null,\"city\":\"L*******s\",\"state\":\"**\",\"postalCode\":\"8***8\",\"country\":\"**\",\"billing\":{\"paymentMethods\":[{\"paymentType\":\"Credit Card\",\"cardHolderName\":\"K******k\",\"billingAddress1\":\"8********s\",\"billingAddress2\":null,\"billingCity\":\"L*******s\",\"billingState\":\"**\",\"billingPostalCode\":\"8***8\",\"billingCountry\":\"**\",\"creditCardType\":\"Mastercard\",\"creditCardNumber\":\"5**************5\",\"creditCardExpireMonth\":\"**\",\"creditCardExpireYear\":\"**\"\"currencyCode\":\"USD\",\"transactionChargeAmount\":134.7}]},\"products\":{\"rooms\":[{\"confirmationNumber\":\"PENDING\",\"guestName\":\"Ka\",\"hotelName\":null,\"roomName\":\"Delano Two Queen Suite\",\"roomId\":\"ROOMCD-v-SQST-d-PROP-v-MV275\",\"checkInDate\":\"2024-08-13\",\"checkOutDate\":\"2024-08-14\",\"guests\":2,\"offerId\":null,\"offerName\":null,\"roomTotal\":134.7,\"depositDue\":134.7,\"totalRoomCharges\":118.8,\"taxes\":0.0,\"resortFeeAndTaxes\":0.0,\"additionalCharges\":0.0,\"propertyId\":\"e0f70eb3-7e27-4c33-8bcd-f30bf3b1103a\"}],\"events\":null}}}",

I have tried converting string to Json and then parse still it gives null or error

[lbendlin]

Your payload is malformed.  Fix the errors there, then the parser works just fine.

let
    Source = "{""transaction"":{""transactionId"":""3d528dbe-d655-4e8a-9661-a491e314e7f4"",""inauthTransactionId"":""e9a6c1915b2b34c06821ac27ed78cdde2955"",""sessionId"":""5ec1e7a6-99f8-48dc-846f-e4aa19944162"",""confirmationNumbers"":""PENDING"",""bookingType"":""GLOBAL"",""paymentFailuresCount"":0,""businessFailuresCount"":0,""transactionType"":""Call-Center"",""transactionDateTime"":""2024-08-13T16:12:52-0400"",""transactionTotalAmount"":178.92,""salesChannel"":null,""ipAddress"":""127.0.0.1"",""processorResponseText"":null,""orderStatus"":null,""guest"":{""loggedIn"":""No"",""memberId"":"""",""firstName"":""K***m"",""lastName"":""**"",""emailAddress"":""k****************m"",""phone"":""(************7"",""createdDate"":null,""lastModifiedDate"":null,""address1"":""8********s"",""address2"":null,""city"":""L*******s"",""state"":""**"",""postalCode"":""8***8"",""country"":""**"",""billing"":{""paymentMethods"":[{""paymentType"":""Credit Card"",""cardHolderName"":""K******k"",""billingAddress1"":""8********s"",""billingAddress2"":null,""billingCity"":""L*******s"",""billingState"":""**"",""billingPostalCode"":""8***8"",""billingCountry"":""**"",""creditCardType"":""Mastercard"",""creditCardNumber"":""5**************5"",""creditCardExpireMonth"":""**"",""creditCardExpireYear"":""**"",""currencyCode"":""USD"",""transactionChargeAmount"":134.7}]},""products"":{""rooms"":[{""confirmationNumber"":""PENDING"",""guestName"":""Ka"",""hotelName"":null,""roomName"":""Delano Two Queen Suite"",""roomId"":""ROOMCD-v-SQST-d-PROP-v-MV275"",""checkInDate"":""2024-08-13"",""checkOutDate"":""2024-08-14"",""guests"":2,""offerId"":null,""offerName"":null,""roomTotal"":134.7,""depositDue"":134.7,""totalRoomCharges"":118.8,""taxes"":0.0,""resortFeeAndTaxes"":0.0,""additionalCharges"":0.0,""propertyId"":""e0f70eb3-7e27-4c33-8bcd-f30bf3b1103a""}],""events"":null}}}}",
       #"Parsed JSON" = Json.Document(Source),
    transaction = #"Parsed JSON"[transaction]
in
    transaction

[mtembhurne]

Thank You Ibendlin....do you mean should fix errors at data source 

[lbendlin]

Maybe the error was caused by you copy/pasting and masking data.

Take the raw output, replace backslash with doublequote, then parse JSON.

[mtembhurne]

Can u help me with M code to replace all \ before parsing 

[lbendlin]

Read about "Value.Is"  and how to use it in Power Query to infer the data type.