M формула для добавления отсутствующих дат в таблицу

Question

Предположим, у меня есть таблица дат PowerBI, где отсутствуют некоторые даты, аналогичные приведенным ниже:

|---------------------|------------------|
|        Date         |     quantity     |
|---------------------|------------------|
|      1/1/2015       |        34        |
|---------------------|------------------|
|      1/4/2015       |        34        |
|---------------------|------------------|

Существует ли формула М, которая добавит отсутствующие строки даты (и просто вставит нулевое значение для второго столбца), что приведет к таблице, как показано ниже:

|---------------------|------------------|
|        Date         |     quantity     |
|---------------------|------------------|
|      1/1/2015       |        34        |
|---------------------|------------------|
|      1/2/2015       |       null       |
|---------------------|------------------|
|      1/3/2015       |       null       |
|---------------------|------------------|
|      1/4/2015       |        34        |
|---------------------|------------------|

Я знаю, что это может быть достигнуто путем объединения полной таблицы [дат] с моим набором данных, но это не вариант в моем сценарии. И мне нужно сделать это в M, во время манипулирования запросами, а не в DAX.

Ценю помощь!

Answer 1

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

in Combined

Answer 2

Вот еще один способ:

Я начинаю с таблицы Table2 на листе Excel и использую ее в качестве источника. Это выглядит так:

Затем используйте PowerBI Get Data, затем выберите " Все" > " Excel" и кнопку " Подключиться", затем перейдите к файлу Excel, в котором таблица будет использоваться в качестве источника, выберите ее и нажмите " Открыть". Затем я выбираю Table2 (имя таблицы, которую я хочу использовать) из таблиц, представленных для выбора, и нажимаю кнопку " Изменить". Это загружает Table2 как мой источник.

Вторая и третья строки в моем М-коде ниже (Source и Table2_Table) - это то, что сгенерировано из вышеперечисленных шагов, и возвращает меня к таблице и загружает ее. Они будут отличаться для вас, в зависимости от вашего источника информации. Ваш исходный путь, информация о файле и имена таблиц будут другими.

let
Source = Excel.Workbook(File.Contents("mypath\myfile.xlsx"), null, true),
Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
#"Generate Dates" = List.Generate(()=> Date.From(List.Min(Table2_Table[Date])), each _ <= Date.From(List.Max(Table2_Table[Date])), each Date.AddDays(DateTime.Date(_), 1)),
#"Converted to Table" = Table.FromList(#"Generate Dates", Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Merged Queries" = Table.NestedJoin(#"Converted to Table",{"Date"},Table2_Table,{"Date"},"Converted to Table",JoinKind.LeftOuter),
#"Expanded Converted to Table" = Table.ExpandTableColumn(#"Merged Queries", "Converted to Table", {"Quantity"}, {"Quantity"})
in
#"Expanded Converted to Table"

Я получаю эту таблицу в качестве вывода:

Который я могу затем использовать в PowerBI. Например, в такой таблице:

PS Я заметил, что при использовании этого в PowerQuery только из Excel, а не из PowerBI, мне нужно явно изменить тип полей даты, иначе слияние не будет работать правильно, а числа не появятся. Поэтому, если вы делаете это только из Excel, а не из PowerBI, это изменение кода работает:

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Generate Dates" = List.Generate(()=> Date.From(List.Min(#"Changed Type"[Date])), each _ <= Date.From(List.Max(#"Changed Type"[Date])), each Date.AddDays(DateTime.Date(_), 1)),
#"Converted to Table" = Table.FromList(#"Generate Dates", Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1",{"Date"},#"Changed Type",{"Date"},"Converted to Table",JoinKind.LeftOuter),
#"Expanded Converted to Table" = Table.ExpandTableColumn(#"Merged Queries", "Converted to Table", {"Quantity"}, {"Quantity"})
in
#"Expanded Converted to Table"

Конечно, было бы неплохо явно назначать типы дат при работе в PowerBI... на всякий случай.