The template language function 'split' expects its first parameter to be of type string. The provided value is of type 'Null'

[walric]

Hi, 

so first off very new to power automate flows and struggling with an error message resulting from null values in a SharePoint list items. I have a list with several columns of data. I'm then splitting each value for each column in order to compose them all together back into one email.  I however keep getting the below error message as some of the values are empty. if anyone help would be greatly appreciated. I've tried sourcing other topics and failed in my attempts to resolve. 

"Unable to process template language expressions for action 'Apply_to_each_3' at line '0' and column '0': 'The template language function 'split' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#split for usage details.'."

Cut down version of flow below. Appreciate this may not be the most efficient way to do it either so if there is a better way then please suggest. 

Many Thanks

[Pstork1]

One of the columns you are splitting has some null values in SharePoint.  To fix that you can replace the column reference with a Coalesce() function where the column is the first value and an empty string is the second.  Coalesce will return the first non-null value from the parameters list it is provided with.  So you will pass an empty string to the Split instead of a null.  If the empty string doesn't work you may need to pass it some default that will work with your split

[walric]

Thanks Pstork. 

I've tried the below expression to replace the split expression but whilst the flow completes, the final output now returns no values when for any values for that column. Is there something Im missing?

split(coalesce(triggerBody()?['Highlights']?['Value'],''),',')

Thanks 

[Pstork1]

That means the function fixed the error, but there is something else wrong with the logic of your flow.  But your original screenshot is too small to read.  Could you post again in chunks and I'll try to help figure out what else is wrong.

[walric]

Thank you Pstork1 for having a look. See breakdown below. 

There's no issue with the recurrence or items. 

I then initialize a variable

I then apply for each value splitting the data with the below expression.

split(coalesce(triggerBody()?['Highlights']?['Value'],''),',')

then compose this

and then use the output of the above into an HTML email

I do this for multiple columns of data but the same same logic. 

Thanks again

[Pstork1]

Are the Compose and Send an email inside the Apply to each loop or after?

Is the Current Item you are embedding from the main Apply to each loop of the second inner loop?

What does a sample record look like?

[walric]

Are the Compose and Send an email inside the Apply to each loop or after? 
No, both outside the apply to each loop

Is the Current Item you are embedding from the main Apply to each loop of the second inner loop?

not 100% sure on the question, however I think the answer is Yes the Current Item is within each second inner loop. See below

Each second loop has a different expression i.e. first "split(coalesce(triggerBody()?['Highlights']?['Value'],''),',')" is for the Highlights Column and second "split(coalesce(triggerBody()?['']?['Value'],'')OperationalIncidents,',')" is for the Operational Incidents column. Each of these have their own initialize variabl i.e. Highlights is A and OperationalIncidents is B. 

What does a sample record look like?

test data below. Each record for each column may have multiple lines of data but my initial issue was it couldn't handle empty values. 

[Pstork1]

You are splitting the column based on a comma, but I don't see any comma's in the columns that you are splitting.  Also, in the sample data Highlights and operational insights don't look like choice columns. Are they?  The use of Value would suggest they are, but the data doesn't look like it.

When you hover the mouse over each current Item what is displayed in the tooltip?

[walric]

sorry provided the wrong data set. See below.

The data is populated from a MS form. All columns are multiple lines of text. None or choice. 

When there is data in each column the below expression works and when appended to the string it formats the data into separate lines. It just cant handle where there is an item in the column which has no value. 

split(item()?['OperationalIncidents'], ',')

However based on the new below expression no data is returned at all even though there is data for some of the items in the column.  

split(coalesce(triggerBody()?['OperationalIncidents']?['Value'],''),',')

When I hover the mouse over each current Item it shows item()

[Pstork1]

I see <br> line breaks in the data, but I'm still not seeing any commas, which is what you are splitting on.  Make sure the value you are supplying to the coalesce as the default value is something that actually works for the Split(). Otherwise your inner loop won't run when you have a blank.  That's probably why you are getting a blank value.

[walric]

HI Pstork1,

I dont think its the split function which is the issue. Ive amended the split to be on <br/> line breaks rather than commas but still returning all blanks. 

the first inner loop returns no input with the coalesce expression split(coalesce(triggerBody()?['OperationalIncidents']?['Value'],''),'<br>')

A = Operational Incident column

the next inner loop where I dont have coalesce in the expression split(item()?['ContractRenewals'], '<br/>') returns values. However cant handle the null value. 

C = Contract Renewals

Data below. All columns the same i.e. multiple lines of text

Im not sure if Im missing something obvious? 

[Pstork1]

Try this instead of the code you are using

split(coalesce(triggerBody()?['OperationalIncidents']?['Value'],' <br>'),'<br>')

The problem is that if triggerBody()?['OperationalIncidents']?['Value'] is null then you are actually running split('','<br>') with you code.  That will return a null array value so the inner loop will never execute.  YOu need to substitute something in the Coalesce that will be a valid value.  Using ' <br>' in the coalesce will still let the Split run and return a single value array that is blank.

[walric]

Hi Pstork1, 

the suggested code unfortunately didn't work. It seems to be replacing all data with the ' <br>' value.

For example in the Operational Incidents column (Apply to each 2) the first entry has data populated and the rest blank. however its not returning this data and returning ' <br>' value for all.  In the Highlights column (Apply to each 3) all entries have data populated but returning ' <br>' value for all. 

1 of 9

2 of 9 

Data again

[Pstork1]

I suggest you add a Compose at each level of the loop to see exactly what data is being provided.  If the code I provided is replacing everything with <br> that would suggest the values you are trying to split on are all null.  So add a Compose just prior to the action where you are doing the split and look at exactly what is being provided to the split.  I suspect there is something wrong with your logic or the way you are retriveing the values.