cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
taubry
Advocate II
Advocate II

GroupBy in dataTable

Good morning !

 

I have a problem with my datatable.

Here are these items:

With(
    {
        groupByFamille: GroupBy(
            Filter(
                Gestion;
                'Exercice budgétaire' = ddBudg_1.Selected.Value || ddBudg_1.Selected.Value=Blank();
                Société = ddSociété_1.Selected.Value || ddSociété_1.Selected.Value=Blank()
            );
            "Famille_x0020_d'achat";
            "Result"
        )

    };
    AddColumns(
        groupByFamille;
        "SumPerFamille";
        Sum(
            Result;
            'Montant HT'
        );
        "DepAuto";
        If(
            Procédure_x0020_applicable = "Demande de devis (Seuil 1)";
            "-";
            Sum(
                Result;
                'Montant HT'
            ) * 0,2
        );
        "DepRea";
        If(Procédure_x0020_utilisée="Petit lot";
        Sum(
                Result;
                'Montant HT'
            )
        )
    )
)

 

I filter at first with 2 selectors. Then I want to add columns. The functions work but power apps does not recognize "Procédure_x0020_utilisée" and "Procédure_x0020_applicable"...

 

it works when i implement them in the GroupBy but it creates too many lines and I just want to create one line per "Family_x200_achat".

 

Ask me questions if my explanation is wrong.

 

Thanks in advance !

 

 

11 REPLIES 11
WarrenBelz
Super User
Super User

Hi @taubry ,

You cannot use GroupBy in a data table - it will work fine in a gallery.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

However, this is the solution I was offered in this post:

https://powerusers.microsoft.com/t5/Building-Power-Apps/linked-one-dataTable-to-the-rows-of-another/...

And it works the columns are well created with the right values ​​but I shouldn't have that many rows but just the "Famille_x200_achats" rows.

taubry
Advocate II
Advocate II

Only "Procedure_x0020_applicable" and "Procedure_x0020_used" are not recognized.

They are if I add "Procedure_x0020_applicable"; "Procedure_x0020_used" under "Family_x200_d'achat":

With(
    {
        groupByFamille: GroupBy(
            Filter(
                Gestion;
                'Exercice budgétaire' = ddBudg_1.Selected.Value || ddBudg_1.Selected.Value=Blank();
                Société = ddSociété_1.Selected.Value || ddSociété_1.Selected.Value=Blank()
            );
            "Famille_x0020_d'achat";
            "Procédure_x0020_applicable";
            "Procédure_x0020_utilisée";
            "Result"
        )

    };
    AddColumns(
        groupByFamille;
        "SumPerFamille";
        Sum(
            Result;
            'Montant HT'
        );
        "DepAuto";
        If(
            Procédure_x0020_applicable = "Demande de devis (Seuil 1)";
            "-";
            Sum(
                Result;
                'Montant HT'
            ) * 0,2
        );
        "DepRea";
        If(Procédure_x0020_utilisée="Petit lot";
        Sum(
                Result;
                'Montant HT'
            )
        )
    )
)

 But that creates too many lines. I would like the items to be grouped together only with "Famille_x200_d'achat"...

@taubry ,

If you really want a data table and one group, just group by the first item Famille_x0020_d'achat and Result then use DropColumns on Result, but why not simply use a gallery?

Thank you for your answer.

I don't use a gallery because I have never done a gallery in table form (I want to keep the form of a table), I will see if it is not too complicated.

 

Regarding your alternative solution, I don't understand, why do you advise to exclude "result" with drop? how?

I did the same with a gallery and indeed it is quite simple.

But, I have the same error as at the beginning with the form. If I only group together with "Famille_x0020_d'achat" the others are not recognized.

taubry
Advocate II
Advocate II

I tried with a gallery:

Gallery.item:

With(
    {
        groupByFamille: GroupBy(
            Filter(
                Gestion;
                'Exercice budgétaire' = ddBudg_2.Selected.Value || ddBudg_2.Selected.Value = Blank();
                Société = ddSociété_2.Selected.Value || ddSociété_2.Selected.Value = Blank()
            );
            "Famille_x0020_d'achat";
            "Result"
        )
    };
    AddColumns(
        groupByFamille;
        "SumPerFamille";
        Sum(
            Result;
            'Montant HT'
        );
        "DepAuto";
        If(
            Procédure_x0020_applicable = "Demande de devis (Seuil 1)";
            "-";
            Sum(
                Result;
                'Montant HT'
            ) * 0,2
        );
        "DepRea";
        If(
            Procédure_x0020_utilisée = "Petit lot";
            Sum(
                Result;
                'Montant HT'
            )
        )
    )
)

 But  "Procédure_x0020_utilisée" and "Procédure_x0020_applicable" are underlined in red (error)

@taubry ,

I assume you understand the GroupBy function (Result is still needed as the "Table").

With(
   {
      groupByFamille: 
      GroupBy(
         Filter(
            Gestion;
            (
               Len(ddBudg_1.Selected.Value) = 0 ||
              'Exercice budgétaire' = ddBudg_1.Selected.Value
            ) &&  
               Len(ddSociété_1.Selected.Value) = ||
               Société = ddSociété_1.Selected.Value
            )
         );
         "Famille_x0020_d'achat";
         "Result"
     )
   };    
   AddColumns(
      groupByFamille;
      "SumPerFamille";
      Sum(
         Result;
         'Montant HT'
     );
     "DepAuto";
     If(
        Procédure_x0020_applicable = "Demande de devis (Seuil 1)";
        "-";
        Sum(
           Result;
           'Montant HT'
        ) * 0,2
     );
     "DepRea";
     If(
         Procédure_x0020_utilisée="Petit lot";
         Sum(
            Result;
            'Montant HT'
         )
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

 

 

 

This code does not work. Always the same error ...

The two columns present in "Gestion" are not recognized. ( "Procédure_x0020_utilisée" and "Procédure_x0020_applicable")

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (2,605)