Help making an inventory calendar table in Power query Editor in M

[DBrito79]

Hello.

I have a huge table "SOWS" wich each line represents one animal.
In each line I have columns with the ID of the animal (unique ID), a "EntryDate" of the animal in the farm and a "OutDate" wich is the date whre the animal leave the farm.

Heres the table:

I have made in DAX this 3 cumulatives measures to make a day inventory :

For entrys 
CALCULATE(

    [Matrizes Entradas (Total)],          // Count of lines of animals because each line has to have an entry

    FILTER(

        ALL('Calendar'),

        'Calendar'[Data] <= MAX('Calendar'[Data]

    )))

for Outs :

CALCULATE(

    [Matrizes Saidas (Total)],  // counts de number of Outdates

    FILTER(

        ALL('Calendar'),

        'Calendar'[Data] <= MAX('Calendar'[Data]

             )) )

And for the inventory =

CALCULATE(

    [Entry]-[Outs],

    FILTER(

        ALL('Calendar'),

        'Calendar'[Data] <= MAX('Calendar'[Data]

    )))

And it works! But the performance is impossible with DAX - Its millions of lines... and millions of time showing a matrix table in Power BI... So i would like to try to do that in Power Query Editor with M language like a calendar table but putting this 6 columns:

Date | 
Nº of entrys (counts) |
Nº of cumulative entries since day 0 of the farm |
Nº of Outs (counts) |
Nº of cumulative Outs since day 0 of the farm |
Inventory of each day ( Nº of cumulative entries since day 0 of the farm  - Nº of cumulative Outs since day 0 of the farm)
 
I think if i have that table, i could work easly and faster with inventory KPIs, Filters... 
Can anybody help me? I don´t understand much of M language. I tried this (above) reading online but i am too far of what i want. 

let
// Creation of the calendar table
InitialDate = #date(2000, 1, 1),
FinalDate = Date.From(DateTime.LocalNow()),
DateList = List.Dates(InitialDate, Number.From(FinalDate - InitialDate) + 1, #duration(1,0,0,0)),
CalendarTable = Table.FromList(DateList, Splitter.SplitByNothing()),
RenameColumns = Table.RenameColumns(CalendarTable,{{"Column1", "Date"}}),

// Importing the SOWS table (already imported and named as "SOWS")
SOWS = SOWS,

// Merge the two tables
MergedTable = Table.NestedJoin(RenameColumns, {"Date"}, SOWS, {"EntryDate"}, "Union", JoinKind.LeftOuter),
ExpandUnion = Table.ExpandTableColumn(MergedTable, "Union", {"EntryDate"}),

// Count entries and exits
CountEntries = Table.Group(ExpandUnion, {"Date"}, {{"Entries", each Table.RowCount(_), type number}}),
CountExits = Table.Group(SOWS, {"OutDate"}, {{"Exits", each Table.RowCount(_), type number}}),

// Merge the counts
FinalTable = Table.Join(CountEntries, "Date", CountExits, "OutDate", JoinKind.LeftOuter),
#"Removed Columns" = Table.RemoveColumns(FinalTable,{"OutDate"}),

#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Entries", Int64.Type}, {"Exits", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Entries", "Exits"})
in
#"Replaced Value"

Thanks in advance

BestRegards

[DBrito79]

Thank you very much
It's excellent!!! it worked perfectly (I just had to change the name of the [ID] column to the correct one that I got wrong)
Thanks again for your quick and assertive response.!!

Even so, I'm worried that the system really does take a long time to process everything... And I'm afraid that with daily updates the process will take too many time. Tests are needed.

One question: Is it possible to block the process in the Power Query editor, for example, from the last 3 months to the whole past (there's data going back to 2002) in order to save time processing lines since it won't change any more? 

[jgeddes]

Without understanding the source of your data, the only suggestion I could give you would be to create a 'sowsInventory' query for each historic year (or groups of historic years). Run the query to get the values and then set the query to not be included in the report refresh and to not load into the report.
You would require a query that is included in the report refresh for the current year. This approach would require you to create a 'sowsInventoryTotal' query where you use Table.Combine to bring in all the historic and current queries. This should run 'quicker' but I am not sure that I would use the term 'fast'. 

Hope this helps.

[DBrito79]

Thanks! I understood perfectly! That will help, and it will work for sure.
I make a query for the lasts years and one for this year and the previous year! 
Thanks again for your suggestion!  
Regards

[jgeddes · Accepted solution]

You can try adding this query. You will need to adjust the start date of the 'List.Dates' step (currently Dec 1, 2023) along with the number of days you want in the table (currently 180).

let
    Source = 
    List.Dates(
        #date(2023,12,01), 
        180, 
        #duration(1,0,0,0)
    ),
    convertDatesToTable = 
    Table.FromList(
        Source, 
        Splitter.SplitByNothing(), 
        null, 
        null, 
        ExtraValues.Error
    ),
    changeToDateType = 
    Table.TransformColumnTypes(
        convertDatesToTable,
        {{"Column1", type date}}
    ),
    renameColumn = 
    Table.RenameColumns(
        changeToDateType,
        {{"Column1", "Date"}}
    ),
    addDailyEntries = 
    Table.AddColumn(
        renameColumn, 
        "dailyEntries", 
        (x)=>List.Count(Table.SelectRows(SOWS, each [EntryDate] = x[Date])[ID]
        ), 
        Int64.Type
    ),
    addDailyOuts = 
    Table.AddColumn(
        addDailyEntries, 
        "dailyOuts", 
        (x)=>List.Count(Table.SelectRows(SOWS, each [OutDate] = x[Date])[ID]), 
        Int64.Type
    ),
    addCumulativeIns = 
    Table.AddColumn(
        addDailyOuts, 
        "cumulativeIns", 
        (x)=>List.Count(Table.SelectRows(SOWS, each [EntryDate] <= x[Date])[ID]), 
        Int64.Type
    ),
    addCumulativeOuts = 
    Table.AddColumn(
        addCumulativeIns, 
        "cumulativeOuts", 
        (x)=>List.Count(Table.SelectRows(SOWS, each [OutDate] <= x[Date])[ID]), 
        Int64.Type
    ),
    addInventory = 
    Table.AddColumn(
        addCumulativeOuts, 
        "Inventory", 
        each [cumulativeIns] - [cumulativeOuts], 
        Int64.Type
    )
in
    addInventory