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
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"