Syntax for joining dataverse tables & Flow Collections - Canvas App

[AVTS]

I am having a heck of a time trying to understand the syntax of powerapps.

I have a background in SQL and I am very comfortable with it. This is not SQL :(.

I have an Dataverse Table. My Dataverse was built correctly.And another side collections is created which comes from flow .

Here is what I have.

Dataverse table : Datasource: Dailyinventory

Collections : obstock

Expected Report Output :

I need to display the daily inventory data alongwith unstock from Collections

exisiting formula used for dailyinventory data.

With( {TheTable:Filter(ShowColumns(Dailyinventory,"crf99_sno","crf99_materialdesc","crf99_materialno","createdon","crf99_bbd","crf99_qty"),DateDiff(createdon, Today(),Days) =0)},
ShowColumns(AddColumns(GroupBy(TheTable,"crf99_materialdesc","crf99_materialno","NewGroup"),

"Created On",Last(NewGroup).createdon,
"bbd",Last(NewGroup).crf99_bbd,
"Qty",Sum(NewGroup,crf99_qty)),"Created On","bbd","crf99_materialdesc","crf99_materialno","Qty"))

Need direction and hint to join the both with collections and tables.

Thank you in advance to anyone willing to help me out with this.

[AVTS]

Any resolver available online. @RandyHayes  can you share your suggestion . . 

[RandyHayes · Accepted solution]

@AVTS 

Please consider changing your Formula to the following:

With({TheTable: Filter(Dailyinventory,
                    DateDiff(createdon, Today(),Days) =0
                )},
    AddColumns(
        AddColumns(
            GroupBy(TheTable,
               "crf99_materialdesc", "crf99_materialno", "NewGroup"
            ),
            "Created On", Last(NewGroup).createdon,
            "bbd", Last(NewGroup).crf99_bbd,
            "Qty", Sum(NewGroup, crf99_qty)
            "Unstock", LookUp(obstock, Materialdesc=crf99_materialdesc && Material=crf99_materialno, Unstock)
        ),
        "Diff", Unstock - Qty
    )
)

This should provide the output you are looking for based on the information you provided.

Also, in this scenario, the ShowColumns function is only going to slow down your performance.  I have removed them from the formula as there is no value derived. 

I hope this is helpful for you.

[AVTS]

Thank you RandyHayes