有没有办法将 PowerBI/Power Query 中日期数据类型列中的空值转换为空白? [英] Is there a way to convert nulls to blanks in a date data type column in PowerBI/Power Query?
问题描述
我在 Power BI 中将合并的 SharePoint 列表用于单个合并表.与各种疾病的休息日期相关的三个列:[TestDate1]、[TestDate2]、[TestDate3].
I'm utilizing a combined SharePoint list into a single merged table in power BI. There are three columns related to resting dates for various diseases: [TestDate1], [TestDate2], [TestDate3].
我在自定义列中有这个逻辑:
I have this logic in a custom column:
if [TestDate1] <= [TestDate2] then [TestDate1] else if [TestDate1] <= [TestDate3] then [TestDate1] else if [TestDate2] <= [TestDate1] then [TestDate2] else if [TestDate2] <= [TestDate3] then [TestDate2] else if [TestDate3] <= [TestDate1] then [TestDate3] else if [TestDate3] <= [TestDate2] then [TestDate3] else ""
但是，所有的 TestDates 都是 null.这导致列中出现错误，因为不能在 PowerQuery 中的逻辑函数中使用空值(据我了解)，所以有没有办法可以测试此逻辑以查看它是否会产生预期的结果?
However, all of the TestDates are null. This is causing an error in the column because nulls cannot be used in a logical function in PowerQuery (from my understanding), so is there a way I can test this logic to see if it would produce the expected result?
谢谢！
推荐答案
您可以显式测试公式中的空值，例如
You could explicitly test for nulls in your formula such as
= if [TestDate1] = null then XXX else YYY
但您最好的选择可能是将整个内容包装在 try 中.否则以防返回错误
but your best bet might be to wrap the whole thing in try .. otherwise in case an error is returned
= try if a then b else c otherwise ZZZ
对于您的代码:
= try if [TestDate1] <= [TestDate2] then [TestDate1] else if [TestDate1] <= [TestDate3] then [TestDate1] else if [TestDate2] <= [TestDate1] then [TestDate2] else if [TestDate2] <= [TestDate3] then [TestDate2] else if [TestDate3] <= [TestDate1] then [TestDate3] else if [TestDate3] <= [TestDate2] then [TestDate3] else "" otherwise ZZZZ
其中 ZZZ 是您想要返回的内容(如 null 或 [TestDate1] 或")以代替返回错误
where ZZZ is the thing you'd like to return (like null or [TestDate1] or "") in place of returning an error
这篇关于有没有办法将 PowerBI/Power Query 中日期数据类型列中的空值转换为空白?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
