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 Inde...
Need Help
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"