在Power Query(M)中，有没有一种方法可以强制Text.Combine或Table.CombineColumn

Question

我想合并两列，比如任意类型的第1列和第2列。我想我有三个选择：

1. 使用Table.AddColumn/each Text.Combine
2. 使用表格组合列
3. 使用Table.AddColumn/each(一个自定义函数，用于获取我想要的行为(

下面的代码中说明了这三种方法。

据我所知，我能得到我想要的行为的唯一方法是使用所示的自定义函数。

所以，我的问题是：

有没有办法强制Text.CombineColumns尝试将null与null组合时返回null？

let
Source = #table(
{"Column1","Column2"},
{
{"1",null},
{null,"2"},
{null,null}
}
),
//Merging with Text.Combine makes null and null into ""
merge_using_text_combine = Table.AddColumn(
Source,
"MergeWithTextCombine",
each Text.Combine({Column1,Column2},"")
),
//Merging with Table.CombineColumns - but row three becomes "", not null
merged = Table.CombineColumns(
Source,
{"Column1","Column2"},
Combiner.CombineTextByDelimiter("",QuoteStyle.None),
"MergeWithTableCombineColumns"
),
//function where null & null = null
custom_combine = (columnX as any,columnY as any,optional sep as nullable text) => 
if 
columnX is null and columnY is null 
then 
null 
else 
Text.Combine(
{columnX,columnY},
if sep = null then "" else sep
),

//Combine using a custom function
merge_using_custom_function = Table.AddColumn(
Source,
"UsingCustomFunction",
each custom_combine([Column1],[Column2])
)
//,
//blah = Text.Combine({null,null}), //produces text
//blurp = null & null, //produces null, but so does 1 & null and so does null & 2
//blahblah = Value.Is(blah,Text.Type) //TRUE
in
merge_using_custom_function

Answer 1

不确定这是否是您想要的，但在组合它们之前，我首先测试了[1-1]和[Column2]等于null。注意，我添加了一个新行{"1"，"2"}来测试它：

let
Source = #table(
{"Column1","Column2"},
{
{"1",null},
{null,"2"},
{"1","2"},
{null,null}
}
),
merge_using_text_combine_ListAllTrue = Table.AddColumn(Source, "MergeWithTextCombine", each if List.AllTrue({[Column1]=null,[Column2]=null}) then null  else Text.Combine({[Column1],[Column2]},""))
in
merge_using_text_combine_ListAllTrue