Say I am importing something like the following into PowerBI:
Date | Quantity
|---------------------|------------------|
| 1/1/2018 | 22 |
| 1/3/2018 | 30 |
| 1/4/2018 | 10 |
|---------------------|------------------|
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 |
|---------------------|------------------|
Can this be done in PowerBI?
Help is much appreciated!
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]
)
)
)
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.
Two items below should do it
// 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
and
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"
In Power Query "M" code, see the comments to understand the different steps. Basically, it generates a new table with all the days, takes the Quantity values if avaliable and fill down the gaps.
Hope this help you.
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
