Forum Discussion

Laho's avatar
Laho
Helper I
7 years ago
Solved

Expression.Error: We cannot convert the value {x} to type Text.

Hi,   I have 1 column with id and want to add an extra column webrequest based on that id(parameter). Stuck on the "Expression.Error: We cannot convert the value 5 to type Text.".   Usually you ...
Need Help
  • Laho's avatar
    Laho
    7 years ago

    Was indeed some data that cannot be parsed, but not specifically (only) the id column. Updated the following:

     

      DynamicAPICallColumn = (id) =>

            let 

                Response = Json.Document(
                    Web.Contents("https://api.pipedrive.com/v1/deals/",
                        [RelativePath= "" & Number.ToText(id) & "/products",
                        Query=[start="0", include_product_data="0", api_token="xxxx"]]
                    )
                ),
                Data = Response[data]
            
            in

    Data,
  #"Added Custom1" = Table.AddColumn(#"Removed Errors", "APICall", each DynamicAPICallColumn([id])),

    Works like a charm now. Thanks

dax's avatar
dax
Community Support
7 years ago

Hi Laho,

This might be related to data type, you could try to change type at first , then add column(like below)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyCCTOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type number}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [id])
in
#"Added Custom"

You could refer to  Power Query Error Expression.Error: We cannot convert the value .. to type Text for details.

Best Regards,

Zoe Zhi

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Laho's avatar
Laho
Helper I
7 years ago

Was indeed some data that cannot be parsed, but not specifically (only) the id column. Updated the following:

 

  DynamicAPICallColumn = (id) =>

            let 

                Response = Json.Document(
                    Web.Contents("https://api.pipedrive.com/v1/deals/",
                        [RelativePath= "" & Number.ToText(id) & "/products",
                        Query=[start="0", include_product_data="0", api_token="xxxx"]]
                    )
                ),
                Data = Response[data]
            
            in

    Data,
  #"Added Custom1" = Table.AddColumn(#"Removed Errors", "APICall", each DynamicAPICallColumn([id])),

Works like a charm now. Thanks