Forum Discussion
Helper IVText.Combine skip nulls
Hi Guys,
I am adding column and sorting values within cell values with | :
= Table.AddColumn(#"Added Custom2", "NewColumnName", each Text.Combine(List.Sort(Text.Split([ExisitngColumnName],"|")),"|"))
However ExisitngColumnName contains few nulls which create Erros in newly created column NewColumnName
Do you know perhaps know how to skip/ignore that Errors please ?
I got it:
= Table.AddColumn(#"Added Custom2", "NewColumnName", each if [ExisitngColumnName] <> null then Text.Combine(List.Sort(Text.Split([ExisitngColumnName],"|")),"|") else "Empty Cell")
5 Replies
- amitchandak
Super User
MasterSonic , use List.RemoveNulls
Table.AddColumn(#"Added Custom2", "NewColumnName", each Text.Combine(List.Sort(List.RemoveNulls(Text.Split([ExisitngColumnName],"|"))),"|"))
https://docs.microsoft.com/en-us/powerquery-m/list-removenulls
MasterSonicI still have this:
Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]Any Idea?
- MasterSonic
Ah... I think I see what's going on.
RemoveNulls takes null out of the list.I meant that I have few cells with null value and they should be skipped ?
Is it possible?
- MasterSonic
I got it:
= Table.AddColumn(#"Added Custom2", "NewColumnName", each if [ExisitngColumnName] <> null then Text.Combine(List.Sort(Text.Split([ExisitngColumnName],"|")),"|") else "Empty Cell")
v-cazheng-msftCommunity Support
Hi MasterSonic,
May I know whether your problem has been resolved? If so, could you please mark your solution by Accept it as the solution to help the other members find it more quickly. Thanks in advance!
Best Regards,
Community Support Team _ Caiyun
