Forum Discussion
Text.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
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
I still have this:
Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]Any Idea?
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?
I got it:
= Table.AddColumn(#"Added Custom2", "NewColumnName", each if [ExisitngColumnName] <> null then Text.Combine(List.Sort(Text.Split([ExisitngColumnName],"|")),"|") else "Empty Cell")
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
