topic Re: Text.Combine skip nulls in DAX Commands and Tips

Text.Combine skip nulls

MasterSonic — Thu, 08 Sep 2022 10:39:33 GMT

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 ?

Re: Text.Combine skip nulls

amitchandak — Thu, 08 Sep 2022 10:57:45 GMT

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

Re: Text.Combine skip nulls

MasterSonic — Thu, 08 Sep 2022 11:20:13 GMT

I still have this:

Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]

Any Idea?

Re: Text.Combine skip nulls

MasterSonic — Thu, 08 Sep 2022 12:15:07 GMT

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?

Re: Text.Combine skip nulls

MasterSonic — Thu, 08 Sep 2022 12:56:55 GMT

I got it:

= Table.AddColumn(#"Added Custom2", "NewColumnName", each if [ExisitngColumnName] <> null then Text.Combine(List.Sort(Text.Split([ExisitngColumnName],"|")),"|") else "Empty Cell")

Re: Text.Combine skip nulls

v-cazheng-msft — Fri, 09 Sep 2022 02:48:19 GMT

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