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

[Laho]

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 would say something is wrong with my id column. But did correct formatting, removed blanks and errors. The parameter seems to be correct, as the error returns the correct id.

     DynamicAPICallColumn = (id as text) as list=>

            let 

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

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

Been trough a lot. Maybe some can assist me. 

[dax]

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 · Accepted solution]

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