Use LookUp fields in GroupBy function

Question

I want to use the LookUp fields - Location & Bin - but facing error in that.

Here's the working code that in my canvas app.

Filter(
    AddColumns(
        GroupBy(
            BinData,
            "bincode",      //old text field
            "location",     //old text field
            "productname",
            "uom",
            "GroupBins"
        ),
        "TQ",
        Sum("eachcount")
    ),
    Not(IsBlank(bincode))
)

To which I want to use new lookup fields and tried below code but it is throwing an error:

Filter(
    AddColumns(
        GroupBy(
            AddColumns(BinData,"LocationText",Location.'Location Name'),
            AddColumns(BinData,"BinText",Bin.'Bin Name'),
            "BinText",
            "LocationText",
            "productname",
            "uom",
            "GroupBins"
        ),
        "TQ",
        Sum("cr5fa_culebraeachcount")
    ),
    Not(IsBlank(BinText))
)

What can I do to fix this issue?

Thanks.

Answer 1

You didn't share the error message, but if I guess correctly, the issue is with your second argument in the GroupBy() function. GroupBy() expects a data source, and then column names as text. Instead you put another AddColumns for the second argument, which returns a table, creating a data type mismatch.

You need to merge your AddColumns functions (e.g. convert both lookup columns in one step):

Filter(
AddColumns(
    GroupBy(
        AddColumns(BinData,"LocationText",Location.'Location Name',"BinText",Bin.'Bin Name'),
        "BinText",
        "LocationText",
        "productname",
        "uom",
        "GroupBins"
    ),
    "TQ",
    Sum("cr5fa_culebraeachcount")
),
Not(IsBlank(BinText))

)

Sometimes the virtual columns we add using AddColumns need to reference each other. In such cases, you can nest AddColumns().