有没有办法将 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屋！
