CountIfs() Equivalent in Power Query M, counts per row within self










1















I'm attempting to implement countifs() like logic in a Power Query query, but not in a summary table referencing a source table. Rather I want to display all records and have it to be another column (in my actual use case this is necessary). Here's what I mean by that...



Input Data:



ID | Animal | Color
-- | ------ | -----
1 | Zebra | Red
2 | Zebra | Blue
3 | Zebra | Red
4 | Zebra | Red


Desired Output:



ID | Animal | Color | Count of others with same color
-- | ------ | ----- | -------------------------------
1 | Zebra | Red | 3
2 | Zebra | Blue | 1
3 | Zebra | Red | 3
4 | Zebra | Red | 3


In an excel inline formula, to calculate the column "Count of others with same color" I would use



=COUNTIFS([Animal],[@Animal],[Color],[@Color])


How could I do this in Power Query, using the M language?










share|improve this question


























    1















    I'm attempting to implement countifs() like logic in a Power Query query, but not in a summary table referencing a source table. Rather I want to display all records and have it to be another column (in my actual use case this is necessary). Here's what I mean by that...



    Input Data:



    ID | Animal | Color
    -- | ------ | -----
    1 | Zebra | Red
    2 | Zebra | Blue
    3 | Zebra | Red
    4 | Zebra | Red


    Desired Output:



    ID | Animal | Color | Count of others with same color
    -- | ------ | ----- | -------------------------------
    1 | Zebra | Red | 3
    2 | Zebra | Blue | 1
    3 | Zebra | Red | 3
    4 | Zebra | Red | 3


    In an excel inline formula, to calculate the column "Count of others with same color" I would use



    =COUNTIFS([Animal],[@Animal],[Color],[@Color])


    How could I do this in Power Query, using the M language?










    share|improve this question
























      1












      1








      1


      1






      I'm attempting to implement countifs() like logic in a Power Query query, but not in a summary table referencing a source table. Rather I want to display all records and have it to be another column (in my actual use case this is necessary). Here's what I mean by that...



      Input Data:



      ID | Animal | Color
      -- | ------ | -----
      1 | Zebra | Red
      2 | Zebra | Blue
      3 | Zebra | Red
      4 | Zebra | Red


      Desired Output:



      ID | Animal | Color | Count of others with same color
      -- | ------ | ----- | -------------------------------
      1 | Zebra | Red | 3
      2 | Zebra | Blue | 1
      3 | Zebra | Red | 3
      4 | Zebra | Red | 3


      In an excel inline formula, to calculate the column "Count of others with same color" I would use



      =COUNTIFS([Animal],[@Animal],[Color],[@Color])


      How could I do this in Power Query, using the M language?










      share|improve this question














      I'm attempting to implement countifs() like logic in a Power Query query, but not in a summary table referencing a source table. Rather I want to display all records and have it to be another column (in my actual use case this is necessary). Here's what I mean by that...



      Input Data:



      ID | Animal | Color
      -- | ------ | -----
      1 | Zebra | Red
      2 | Zebra | Blue
      3 | Zebra | Red
      4 | Zebra | Red


      Desired Output:



      ID | Animal | Color | Count of others with same color
      -- | ------ | ----- | -------------------------------
      1 | Zebra | Red | 3
      2 | Zebra | Blue | 1
      3 | Zebra | Red | 3
      4 | Zebra | Red | 3


      In an excel inline formula, to calculate the column "Count of others with same color" I would use



      =COUNTIFS([Animal],[@Animal],[Color],[@Color])


      How could I do this in Power Query, using the M language?







      excel excel-formula powerquery m






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 14 '18 at 16:48









      ak112358ak112358

      5641924




      5641924






















          2 Answers
          2






          active

          oldest

          votes


















          1














          Use a count on a filtered table.




          Here's what the formula would look like for a custom column:



          List.Count(
          Table.SelectRows(
          #"Previous Step Name Goes Here",
          (C) => [Animal] = C[Animal] and [Color] = C[Color]
          )[ID]
          )


          The () => function construction is required since you need to refer to two separate contexts. One is the row you are evaluating the function within and the other being the row of the table you are filtering with Table.SelectRows. This is a bit weird at first sight, so I recommend searching for "Power Query each function" and doing a bit of reading.



          Note: Appending [ID] to the table transforms it into a list by choosing a single column.






          share|improve this answer























          • If I make #"Previous Step Name Goes Here", the Table name, I get a cyclic reference error which makes sense. So, an important note (for someone used to thinking about spreadsheets rather than queries) is it has to use a copy of the table so that it's not acting on itself.

            – ak112358
            Nov 14 '18 at 17:27






          • 1





            Yes, that's why you reference the previous step in the query, not the name of the query. You don't want circular dependency.

            – Alexis Olson
            Nov 14 '18 at 17:45


















          0














          Similar to Alexis solution but using List functions.



          let
          Source = Excel.CurrentWorkbook()[Name="Test"][Content],
          CountCol = Table.AddColumn(Source, "Count", (r) => List.Count(List.PositionOf(Source[Color],r[Color],Occurrence.All)))

          in
          CountCol


          A new column named "Count" is added To the "Source" table.



          The function gets each record (named "r") from the "Source" table and passes it to a nested function.



          From the inside, "List.PositionOf" gets three arguments: the "Color" column from the "Source" table as a list, the field "Color" from the passed record ("r") and a third optional argument "Occurrence.All" that forces to return all the matches and not only the first one.



          The result of this function is another list with all the positions of the field "Color" from the records in the whole list of colors.



          The outter funtion "List.Count" just counts the elements of the list of positions and returns an integer.






          share|improve this answer






















            Your Answer






            StackExchange.ifUsing("editor", function ()
            StackExchange.using("externalEditor", function ()
            StackExchange.using("snippets", function ()
            StackExchange.snippets.init();
            );
            );
            , "code-snippets");

            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "1"
            ;
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function()
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled)
            StackExchange.using("snippets", function()
            createEditor();
            );

            else
            createEditor();

            );

            function createEditor()
            StackExchange.prepareEditor(
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader:
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            ,
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );













            draft saved

            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305097%2fcountifs-equivalent-in-power-query-m-counts-per-row-within-self%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            1














            Use a count on a filtered table.




            Here's what the formula would look like for a custom column:



            List.Count(
            Table.SelectRows(
            #"Previous Step Name Goes Here",
            (C) => [Animal] = C[Animal] and [Color] = C[Color]
            )[ID]
            )


            The () => function construction is required since you need to refer to two separate contexts. One is the row you are evaluating the function within and the other being the row of the table you are filtering with Table.SelectRows. This is a bit weird at first sight, so I recommend searching for "Power Query each function" and doing a bit of reading.



            Note: Appending [ID] to the table transforms it into a list by choosing a single column.






            share|improve this answer























            • If I make #"Previous Step Name Goes Here", the Table name, I get a cyclic reference error which makes sense. So, an important note (for someone used to thinking about spreadsheets rather than queries) is it has to use a copy of the table so that it's not acting on itself.

              – ak112358
              Nov 14 '18 at 17:27






            • 1





              Yes, that's why you reference the previous step in the query, not the name of the query. You don't want circular dependency.

              – Alexis Olson
              Nov 14 '18 at 17:45















            1














            Use a count on a filtered table.




            Here's what the formula would look like for a custom column:



            List.Count(
            Table.SelectRows(
            #"Previous Step Name Goes Here",
            (C) => [Animal] = C[Animal] and [Color] = C[Color]
            )[ID]
            )


            The () => function construction is required since you need to refer to two separate contexts. One is the row you are evaluating the function within and the other being the row of the table you are filtering with Table.SelectRows. This is a bit weird at first sight, so I recommend searching for "Power Query each function" and doing a bit of reading.



            Note: Appending [ID] to the table transforms it into a list by choosing a single column.






            share|improve this answer























            • If I make #"Previous Step Name Goes Here", the Table name, I get a cyclic reference error which makes sense. So, an important note (for someone used to thinking about spreadsheets rather than queries) is it has to use a copy of the table so that it's not acting on itself.

              – ak112358
              Nov 14 '18 at 17:27






            • 1





              Yes, that's why you reference the previous step in the query, not the name of the query. You don't want circular dependency.

              – Alexis Olson
              Nov 14 '18 at 17:45













            1












            1








            1







            Use a count on a filtered table.




            Here's what the formula would look like for a custom column:



            List.Count(
            Table.SelectRows(
            #"Previous Step Name Goes Here",
            (C) => [Animal] = C[Animal] and [Color] = C[Color]
            )[ID]
            )


            The () => function construction is required since you need to refer to two separate contexts. One is the row you are evaluating the function within and the other being the row of the table you are filtering with Table.SelectRows. This is a bit weird at first sight, so I recommend searching for "Power Query each function" and doing a bit of reading.



            Note: Appending [ID] to the table transforms it into a list by choosing a single column.






            share|improve this answer













            Use a count on a filtered table.




            Here's what the formula would look like for a custom column:



            List.Count(
            Table.SelectRows(
            #"Previous Step Name Goes Here",
            (C) => [Animal] = C[Animal] and [Color] = C[Color]
            )[ID]
            )


            The () => function construction is required since you need to refer to two separate contexts. One is the row you are evaluating the function within and the other being the row of the table you are filtering with Table.SelectRows. This is a bit weird at first sight, so I recommend searching for "Power Query each function" and doing a bit of reading.



            Note: Appending [ID] to the table transforms it into a list by choosing a single column.







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Nov 14 '18 at 17:05









            Alexis OlsonAlexis Olson

            14.2k21734




            14.2k21734












            • If I make #"Previous Step Name Goes Here", the Table name, I get a cyclic reference error which makes sense. So, an important note (for someone used to thinking about spreadsheets rather than queries) is it has to use a copy of the table so that it's not acting on itself.

              – ak112358
              Nov 14 '18 at 17:27






            • 1





              Yes, that's why you reference the previous step in the query, not the name of the query. You don't want circular dependency.

              – Alexis Olson
              Nov 14 '18 at 17:45

















            • If I make #"Previous Step Name Goes Here", the Table name, I get a cyclic reference error which makes sense. So, an important note (for someone used to thinking about spreadsheets rather than queries) is it has to use a copy of the table so that it's not acting on itself.

              – ak112358
              Nov 14 '18 at 17:27






            • 1





              Yes, that's why you reference the previous step in the query, not the name of the query. You don't want circular dependency.

              – Alexis Olson
              Nov 14 '18 at 17:45
















            If I make #"Previous Step Name Goes Here", the Table name, I get a cyclic reference error which makes sense. So, an important note (for someone used to thinking about spreadsheets rather than queries) is it has to use a copy of the table so that it's not acting on itself.

            – ak112358
            Nov 14 '18 at 17:27





            If I make #"Previous Step Name Goes Here", the Table name, I get a cyclic reference error which makes sense. So, an important note (for someone used to thinking about spreadsheets rather than queries) is it has to use a copy of the table so that it's not acting on itself.

            – ak112358
            Nov 14 '18 at 17:27




            1




            1





            Yes, that's why you reference the previous step in the query, not the name of the query. You don't want circular dependency.

            – Alexis Olson
            Nov 14 '18 at 17:45





            Yes, that's why you reference the previous step in the query, not the name of the query. You don't want circular dependency.

            – Alexis Olson
            Nov 14 '18 at 17:45













            0














            Similar to Alexis solution but using List functions.



            let
            Source = Excel.CurrentWorkbook()[Name="Test"][Content],
            CountCol = Table.AddColumn(Source, "Count", (r) => List.Count(List.PositionOf(Source[Color],r[Color],Occurrence.All)))

            in
            CountCol


            A new column named "Count" is added To the "Source" table.



            The function gets each record (named "r") from the "Source" table and passes it to a nested function.



            From the inside, "List.PositionOf" gets three arguments: the "Color" column from the "Source" table as a list, the field "Color" from the passed record ("r") and a third optional argument "Occurrence.All" that forces to return all the matches and not only the first one.



            The result of this function is another list with all the positions of the field "Color" from the records in the whole list of colors.



            The outter funtion "List.Count" just counts the elements of the list of positions and returns an integer.






            share|improve this answer



























              0














              Similar to Alexis solution but using List functions.



              let
              Source = Excel.CurrentWorkbook()[Name="Test"][Content],
              CountCol = Table.AddColumn(Source, "Count", (r) => List.Count(List.PositionOf(Source[Color],r[Color],Occurrence.All)))

              in
              CountCol


              A new column named "Count" is added To the "Source" table.



              The function gets each record (named "r") from the "Source" table and passes it to a nested function.



              From the inside, "List.PositionOf" gets three arguments: the "Color" column from the "Source" table as a list, the field "Color" from the passed record ("r") and a third optional argument "Occurrence.All" that forces to return all the matches and not only the first one.



              The result of this function is another list with all the positions of the field "Color" from the records in the whole list of colors.



              The outter funtion "List.Count" just counts the elements of the list of positions and returns an integer.






              share|improve this answer

























                0












                0








                0







                Similar to Alexis solution but using List functions.



                let
                Source = Excel.CurrentWorkbook()[Name="Test"][Content],
                CountCol = Table.AddColumn(Source, "Count", (r) => List.Count(List.PositionOf(Source[Color],r[Color],Occurrence.All)))

                in
                CountCol


                A new column named "Count" is added To the "Source" table.



                The function gets each record (named "r") from the "Source" table and passes it to a nested function.



                From the inside, "List.PositionOf" gets three arguments: the "Color" column from the "Source" table as a list, the field "Color" from the passed record ("r") and a third optional argument "Occurrence.All" that forces to return all the matches and not only the first one.



                The result of this function is another list with all the positions of the field "Color" from the records in the whole list of colors.



                The outter funtion "List.Count" just counts the elements of the list of positions and returns an integer.






                share|improve this answer













                Similar to Alexis solution but using List functions.



                let
                Source = Excel.CurrentWorkbook()[Name="Test"][Content],
                CountCol = Table.AddColumn(Source, "Count", (r) => List.Count(List.PositionOf(Source[Color],r[Color],Occurrence.All)))

                in
                CountCol


                A new column named "Count" is added To the "Source" table.



                The function gets each record (named "r") from the "Source" table and passes it to a nested function.



                From the inside, "List.PositionOf" gets three arguments: the "Color" column from the "Source" table as a list, the field "Color" from the passed record ("r") and a third optional argument "Occurrence.All" that forces to return all the matches and not only the first one.



                The result of this function is another list with all the positions of the field "Color" from the records in the whole list of colors.



                The outter funtion "List.Count" just counts the elements of the list of positions and returns an integer.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Nov 15 '18 at 18:04









                Daniel HerceDaniel Herce

                213




                213



























                    draft saved

                    draft discarded
















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid


                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.

                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305097%2fcountifs-equivalent-in-power-query-m-counts-per-row-within-self%23new-answer', 'question_page');

                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    這個網誌中的熱門文章

                    How to read a connectionString WITH PROVIDER in .NET Core?

                    Museum of Modern and Contemporary Art of Trento and Rovereto

                    In R, how to develop a multiplot heatmap.2 figure showing key labels successfully