Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cannot convert value null to type List

I have a data set in table as below : 

 

 

 

 

 

The RCA field has the values coming from an API in the form of records as below which i want to show as a single record separated by "-"

"customfield_47426":[{"self":"https://jira.ihsmarkit.com/rest/api/2/customFieldOption/97317","value":"Other","id":"97317"},{"self":"https://jira.ihsmarkit.com/rest/api/2/customFieldOption/102002","value":"Duplicate Issue","id":"102002"}]

So to expand the List i wrote the below logic which works to certain extent 

 

 

= Table.TransformColumns(
    #"Renamed Columns2",
    {
      {
        "RCA", 
        each Combiner.CombineTextByDelimiter(" - ")(
	  List.Transform( _ , each if _ = null or _ = "" then 0 else [value] ) 
        ),
	type text
      }
    }
  )

 

 

This gives the output as below with error for the null values

 

 

 

 

 

 

 

How do i resolve this issue ? I have tried replacing null values with "-" etc but still the same conversion error. I even tried replacing the code as below to insert a string which matches the list record but with empty values but still same error comes.

 

 

= Table.ReplaceValue(#"Renamed Columns2",null,"[{""self"":"""",""value"":""NA"",""id"":""""}]",Replacer.ReplaceValue,{"RCA"})

 

 

 

Tips and Tricks
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    You could try this simple way as below:

    Just add a steps that replace error with null (or other value).

     

    or just nested it in your formula as below:

     

    =Table.ReplaceErrorValues( Table.TransformColumns(
    #"Renamed Columns2",
    {
      {
        "RCA", 
        each Combiner.CombineTextByDelimiter(" - ")(
	  List.Transform( _ , each if _ = null or _ = "" then 0 else [value] ) 
        ),
	type text
      }
    }
  ) , {{"RCA", null}})

     

     

    Regards,

    Lin

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi Anonymous 

    You could try this simple way as below:

    Just add a steps that replace error with null (or other value).

     

    or just nested it in your formula as below:

     

    =Table.ReplaceErrorValues( Table.TransformColumns(
    #"Renamed Columns2",
    {
      {
        "RCA", 
        each Combiner.CombineTextByDelimiter(" - ")(
	  List.Transform( _ , each if _ = null or _ = "" then 0 else [value] ) 
        ),
	type text
      }
    }
  ) , {{"RCA", null}})

     

     

    Regards,

    Lin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    I think the problem is that the "null" value is not list type, even you change it to "-", it's also a string type, not list type.

    So you can't use the list.transform() function to it.

     

    I think you need to do the if-else firstly, for example, just using:

    if RCA is null then 0

    else "your combine / list.transform() function

    to do it.

     

    Please try.

    Aiolos Zhao

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Tried it but still same issue , output http://prntscr.com/qkoyuv

      = if "RCA" is null then 0 else (Table.TransformColumns(
    #"Renamed Columns2",
    {
      {
        "RCA", 
        each Combiner.CombineTextByDelimiter(" - ")(
	  List.Transform( _ , each [value] ) 
        ),
	type text
      }
    }
  ))