Calculate Distinct Count in a Group By operation in Power Query #powerquery #powerbihttp://sqlblog.com/blogs/marco_russo/archive/2014/03/31/calculate-distinct-count-in-a-group-by-operation-in-power-query-powerquery-powerbi.aspxThe current version of Power Query does not have a user interface to create a Distinct Count calculation in a Group By operation. However, you can do this in “M” with a simple edit of the code generated by the Power Query window. Consider the followingenCommunityServer 2.1 SP2 (Build: 61129.1)re: Calculate Distinct Count in a Group By operation in Power Query #powerquery #powerbihttp://sqlblog.com/blogs/marco_russo/archive/2014/03/31/calculate-distinct-count-in-a-group-by-operation-in-power-query-powerquery-powerbi.aspx#53532Mon, 07 Apr 2014 20:36:34 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53532Ben<p>Thanks for the feedback Marco! We've started a thread to talk about this feature based on your post.</p>
<p>- PQ Team</p>
re: Calculate Distinct Count in a Group By operation in Power Query #powerquery #powerbihttp://sqlblog.com/blogs/marco_russo/archive/2014/03/31/calculate-distinct-count-in-a-group-by-operation-in-power-query-powerquery-powerbi.aspx#53533Mon, 07 Apr 2014 22:37:52 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53533Marco Russo (SQLBI)<p>You're a great team - thanks! :)</p>
re: Calculate Distinct Count in a Group By operation in Power Query #powerquery #powerbihttp://sqlblog.com/blogs/marco_russo/archive/2014/03/31/calculate-distinct-count-in-a-group-by-operation-in-power-query-powerquery-powerbi.aspx#53741Fri, 25 Apr 2014 18:30:46 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53741DBExcelAccounting@Blogspot.com<p>Thanks for the post - How about going the other way - I made a function to create an enumerated list type from a comma separated list.</p>
<p>The function takes a list where each item is text in the format integer (for the number of times to enumerate the item) followed by the text string (name, label or number )to be enumerated after the first comma). This way calling the function on {"1,A" "2,XY,Z" "3,3"} gives {"A", "XY,Z", "XY,Z", "3", "3", "3"}</p>
<p>For me this was useful in writing a procedure to do algebraic operations on long lists of number and going from compressed to decompressed data and back. I also have a version that works the same way on a 2 column table the same way.</p>
<p>(x as list) =></p>
<p>let</p>
<p> Source = List.Transform(x,(_)=> [ Count = Text.Start( _ , Text.PositionOf( _ , "," )), Name = Text.End(_,Text.Length(_) - Text.PositionOf(_,",")-1) ]),</p>
<p> InsertedCustom = List.Transform(Source, (_) => enum(_[Name],Number.From(_[Count]),{})),</p>
<p> enum = (x,y,z) => if y <2 then List.Combine({z, {x}}) else iter(x,y,z),</p>
<p> iter = (x,y,z) => enum(x,y-1, List.Combine({z,{x}})),</p>
<p> Output = List.Combine(InsertedCustom)</p>
<p>in</p>
<p> Output</p>
re: Calculate Distinct Count in a Group By operation in Power Query #powerquery #powerbihttp://sqlblog.com/blogs/marco_russo/archive/2014/03/31/calculate-distinct-count-in-a-group-by-operation-in-power-query-powerquery-powerbi.aspx#53807Sun, 04 May 2014 18:31:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53807Marco Russo (SQLBI)<p>Thanks for the function!</p>
re: Calculate Distinct Count in a Group By operation in Power Query #powerquery #powerbihttp://sqlblog.com/blogs/marco_russo/archive/2014/03/31/calculate-distinct-count-in-a-group-by-operation-in-power-query-powerquery-powerbi.aspx#53911Thu, 15 May 2014 02:14:46 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53911Tim Rodman<p>Any thoughts on how to do a COUNT with criteria, like the HAVING clause in SQL?</p>
re: Calculate Distinct Count in a Group By operation in Power Query #powerquery #powerbihttp://sqlblog.com/blogs/marco_russo/archive/2014/03/31/calculate-distinct-count-in-a-group-by-operation-in-power-query-powerquery-powerbi.aspx#63955Fri, 15 Sep 2017 18:26:08 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:63955Tim Cartwright<p>I know this post is several years old, but hopefully someone can see this and answer it. How do you do a group by count like this while leaving all your other columns still in place?</p>