Forum Discussion

eacy's avatar
eacy
Icon for Helper II rankHelper II
8 years ago

Group By together with Distinct Count does not work after Index Column is added

Hi,

I'm trying to add an Index Column before I do a Group By and this makes the Distinct Count return a result that I do not understand.

Small sample to illustrate my problem below:

 

Before Index Column is added, everything work fine. Distinct Count figure out that the value for the property is different and return 2 instead of 1.

 

After the Index Colunm is added the Distinct Count is returning 2 eventhrough the value is still the same for the property belonging to "Food for Money" Client.

 

Is this an error or just me not understanding how Group By works.

I would expect Group By to group and count in the same way eventhrough there is an Index Column displayed.

 

 

Any help is appriciated, thanks

Need Help

5 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Your pictures are too small. Please zoom in on the tables.

     

    Additionally, please post your code as text (you can use the button "Insert code" next to the smiley button)

    • eacy's avatar
      eacy
      Icon for Helper II rankHelper II
      8 years ago

      Just click on the image and potential press the full screen button

  • Bordalos's avatar
    Bordalos
    Icon for Helper I rankHelper I
    8 years ago

    That's is exactly due to the index.  Let's see,

    You want to count distinct rows.

    When you add the index you are making each row distinct. then when you group by  client&property you have a distinct entry for each row. ie,  [food for money]&[Number of Stores]&[1] and [food for money]&[Number of Stores]&[2].

    When you ask how many distinct entries for [food for money]&[Number of Stores] the result is =2 because you have index =1 +

    index = 2.

    Make sense?

     

    • eacy's avatar
      eacy
      Icon for Helper II rankHelper II
      8 years ago

      Hi Bordalos,

       

      Ok, Now after I have played a little more with this, it makes perfectly sense. Stupid me :-)

       

      Now in my real world scenario I got an existing ID and an Index column in the table when I want to make my distict count. Do you have any ideas on how I can get rid of them while I do my distinct count and afterwards get them back again?

       

      BR

      Esben

      • Bordalos's avatar
        Bordalos
        Icon for Helper I rankHelper I
        8 years ago

        Esben, what about you first group by and in the end add the index?

         

        This would be my approach:

        1- Group by Client - count Distinct rows and keep all other rows

        2- Expand other rows 

        3- add index column

         

         

         

        let
         Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvPT1FIyy9SyM3PS61U0lHyK81NSi1SyE9TKC7JL0otBgoZKsXqkKLSF6QArNQvvyQjMy+dCoqNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Client = _t, Property = _t, value = _t]),
         #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Property", type text}, {"value", Int64.Type}}),
         #"Grouped Rows" = Table.Group(#"Changed Type", {"Client"}, {{"Distinct", each Table.RowCount(Table.Distinct(_)), type number}, {"other", each _, type table}}),
         #"Expanded other" = Table.ExpandTableColumn(#"Grouped Rows", "other", {"Property", "value"}, {"Property", "value"}),
         #"Reordered Columns" = Table.ReorderColumns(#"Expanded other",{"Client", "Property", "value", "Distinct"}),
         #"Added Index" = Table.AddIndexColumn(#"Reordered Columns", "Index", 1, 1)
        in
         #"Added Index"