在 Power BI/Power Query 中添加缺失的日期行并取上面行的值 [英] Add missing date rows in Power BI/Power Query and take value of row above

问题描述

假设我将以下内容导入 PowerBI:

Say I am importing something like the following into PowerBI:

          Date        |     Quantity       
|---------------------|------------------|
|       1/1/2018      |        22        |
|       1/3/2018      |        30        |
|       1/4/2018      |        10        |
|---------------------|------------------|

外部源表是一系列日期值行，其中缺少一些日期值.我想执行一些 DAX/M 以将任何缺失的日期行添加到数据集中，其中数量"值取自之前的第一个日期.所以我得到的数据集是这样的:

Where the external source table is a series of date, value rows with some date values missing. I'd like to execute some DAX/M to add any missing date rows into the data set, where 'Quantity' value is taken from the first date prior. So my resulting dataset would like like this:

          Date        |     Quantity       
|---------------------|------------------|
|       1/1/2018      |        22        |
|       1/2/2018      |        22        |
|       1/3/2018      |        30        |
|       1/4/2018      |        10        |
|---------------------|------------------|

这可以在 PowerBI 中完成吗?

Can this be done in PowerBI?

非常感谢您的帮助！

推荐答案

您可以在 DAX 中通过创建一个包含您范围内所有日期的新表来执行此操作，如下所示:

You can do this in DAX by creating a new table with all of the dates in your range as follows:

FullTable = 
ADDCOLUMNS(
    CALENDAR(MIN(Table1[Date]), MAX(Table1[Date])),
    "Quantity",
    LOOKUPVALUE(
        Table1[Quantity],
        Table1[Date],
        MAXX(
            FILTER(Table1, Table1[Date] <= EARLIER([Date])),
            [Date]
        )
    )
)

CALENDAR 函数为您提供原始表中从最小日期到最大日期的日期范围.从那里，我们添加一个新列，Quantity，并将其定义为我们在原始表中查找最大日期的 Quantity 时获得的值发生在当前行中的日期或之前.

The CALENDAR function gives you a range of dates from you minimum to maximum dates in your original table. From there, we add a new column, Quantity, and define it as the value we get when looking up the Quantity in the original table for the date that was the maximum date occurring on or before the date in the current row.

这篇关于在 Power BI/Power Query 中添加缺失的日期行并取上面行的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！