在 Power BI/Power Query 中添加缺少的日期行，并获取上面行的值

Question

假设我正在将以下内容导入PowerBI：

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

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

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

这可以在PowerBI中完成吗？

非常感谢帮助！

Answer 1

您可以在 DAX 中执行此操作，方法是创建一个包含范围内所有日期的新表，如下所示：

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时获得的值。

Answer 2

在Power Query "M"代码中，请参阅注释以了解不同的步骤。基本上，它会生成一个包含所有日期的新表，获取数量值(如果可用(并填补空白。

希望这对你有帮助。

let
Source = Excel.CurrentWorkbook(){[Name="Test"]}[Content],
Converted = Table.TransformColumnTypes(Source,{{"Date", type date}}),
// force conversion to date type to avoid problems if date values comes from an Excel table (in Excel they are numbers).
DatesCol = Converted[Date],
// list from Date column of Converted table
Ini = List.Min(DatesCol),
End = List.Max(DatesCol),
Size = Number.From(End - Ini) +1,
DatesNew = List.Dates(Ini, Size, #duration(1, 0, 0, 0)),
// generates a new list of dates from min to max
NewTable = Table.FromColumns({DatesNew},{"Dates"}),
// new table from new list of dates, column name is "Dates" not "Date".
Joined = Table.Join(NewTable,"Dates",Converted,"Date",JoinKind.FullOuter),
// join both table NewTable and Converted (from Source) and keep all the dates from the DatesNew list.
Removed = Table.RemoveColumns(Joined, "Date"),
// remove the original Date column as it's not needed.
Result = Table.FillDown(Removed,{"Quantity"})
// Fill Down the Quantity column.
in
Result

Answer 3

下面的两个项目应该可以做到

// Query name fnGeT
(TableName as table, DateSearch as date) =>
let Source2 = Table.SelectRows(TableName, each [Date] < DateSearch and [quantity] <> null),
Max= Table.Group(Source2, {}, {{"Max", each List.Max([Date]), type date}}),
MaxDate = Table.SelectRows(Source2, each [Date] = Max{0}[Max]),
Value =  MaxDate{0}[quantity]
in Value

和

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Base = Table.TransformColumnTypes(Source,{{"Date", type date}, {"quantity", Int64.Type}}),
// Generate list of dates between Max and Min dates of Table1
DateRange = Table.Group(Base, {}, {{"MinDate", each List.Min([Date]), type date}, {"MaxDate", each List.Max([Date]), type date}}),
StartDate  = DateRange[MinDate]{0},
EndDate = DateRange[MaxDate]{0},
List ={Number.From(StartDate)..Number.From(EndDate)},
#"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
FullList = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
//Right Anti Join to find dates not in original Table1   
#"Merged Queries" = Table.NestedJoin(Base,{"Date"},FullList,{"Column1"},"Table2",JoinKind.RightAnti),
#"Removed Other Columns" = Table.SelectColumns(#"Merged Queries",{"Table2"}),
Extras = Table.ExpandTableColumn(#"Removed Other Columns", "Table2", {"Column1"}, {"Date"}),
Combined = Base & Extras,
#"Added Custom" = Table.AddColumn(Combined, "Custom", each if [quantity]<>null then [quantity] else fnGet(Combined,[Date])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"quantity"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "quantity"}})
in #"Renamed Columns"