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

[eacy]

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

[Bordalos]

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]

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]

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" 

[MarcelBeug]

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]

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