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 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. 

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

2 Replies

  • 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