cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amkhullar
Helper II
Helper II

Cannot convert value null to type List

I have a data set in table as below : 

 

2.png

 

 

 

 

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

1.png

 

 

 

 

 

 

 

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"})

 

 

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi @amkhullar 

You could try this simple way as below:

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

1.JPG

2.JPG

3.JPG

 

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

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

View solution in original post

3 REPLIES 3
v-lili6-msft
Community Support
Community Support

hi @amkhullar 

You could try this simple way as below:

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

1.JPG

2.JPG

3.JPG

 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AiolosZhao
Memorable Member
Memorable Member

Hi @amkhullar ,

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
      }
    }
  ))

 

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.