0

I have done multiple examples found online, but still I get a null record for the aggregated sum variable. The code makes sense. But for some reason, when I add the collection to the gallery visual, I get the correct groups (the item number), but the 'Sum of qty' added column/aggregated field is coming back empty:

ClearCollect(
     collCustomerSales,
     DropColumns(
          AddColumns(
               GroupBy(myDatasource,"item_no","item_desc","grp2"),
          "Sum of qty",Sum(grp2,qty)
          ),
         "grp2"
         )
    );
sabukaru
  • 5
  • 4
  • 1
    From a first glance this looks correct, but without knowing the data it may be hard to know. You can try "copying" your data source into a local collection (`ClearCollect(temp, {item_no:12,item_desc:"desc 12",qty:3},{item_no:13,item_desc:"desc 13",qty:4},{item_no:12,item_desc:"desc 12",qty:5},...)` and trying your formula with the collection. If it doesn't work, you will have a more complete example to post here; if it works, then you can try to see how your data source differs from the collection. – carlosfigueira May 17 '23 at 21:32
  • Thank you @carlosfigueira. I am not sure I can construct this. I have simplified it even more (drop the item_desc variable and simply group by item_no and sum, no collection being created) and still get the same issue. I am certain that the original datasource is in the correct data types (text for item_no and number for qty) since I can perform operations within Power Apps when using the datasource as is (no GroupBy or other functions). Its only when doing the GroupBy, adding columns, etc. that I start getting issues. Also, the data source is and sql connection. – sabukaru May 18 '23 at 00:17
  • @carlosfigueira I just did what you suggested (manually create a collection). And it works correctly. It aggregates as intended. – sabukaru May 18 '23 at 00:37
  • @carlosfigueira, as you suggested, the problem was in the data set. However, I do not know the exact cause. The problem seems to be that records for an entire variable were being dropped when the collection (from the data source) was created. I had to specify which columns from the data source to include in the collection. Once the collection was built that way, everything worked. – sabukaru May 18 '23 at 12:57
  • It's possible you are hitting a known issue in a feature called 'Explicit column selection'. This is a performance optimization that will only retrieve from the data source the columns that are referenced in the app, but sometimes the logic doesn't understand it. One way to work around this problem is to have a few hidden labels that explicitly refer the columns you are using (something like `First(myDatasource).item_no` and `First(myDatasource).item_desc`). I heard the fix is coming in the second semester of this year (it's a complex analysis engine that is being rewritten). – carlosfigueira May 18 '23 at 14:59
  • 1
    @carlosfigueira That makes total sense, since I saw this kind of behavior. But without the proper knowledge it was really confusing! Thank you again for your help. – sabukaru May 18 '23 at 15:32

1 Answers1

0

I found a solution by, instead of creating the collection straight from the data source:

ClearCollect(collMyColl, data_source)

, I specified which columns to include in the sollection:

ClearCollect(collMyColl, ShowColumns(Filter(data_source, StartsWith(cus_id, varCusId)), "item_no","item_desc","qty"))

Note: Filtering the data_source here simply filters out sales for the selected customer.

sabukaru
  • 5
  • 4