How to get the count of the recipients of a column

[Anonymous]

Hi,

Can someone hlp on the following requirement

Consider below is the data in Table

Date                        Owner                      Filename             Sharedwith                                                        Output
27/07/2020         dery@123.com        test.docx                abc@123.com                                                     4
24/07/2020         dery@123.com         test.docx               xyz@123.com,123@abc.com xyz@mnc.com       4
24/07/2020         yarn @Anonymous.com         test1.ppt                                                                                           3
27/07/2020         yarn @Anonymous.com         test1.ppt               abc@123.com,zz@xyz.in                                    3
23/07/2020          barn@123.com          test2.pdf              xyz@mnc.com abc@123.com                            2

Searching for the Owner and Filename in the complete table and if they are matched in other rows , a new coulmn(Output) to be created in the same table with count of Sharedwith users (Sharedwith even with comma seperated,space seperated)

[Anonymous]

@ryan_mayu . Hi can you help me on this requirement

[ryan_mayu · Accepted solution]

@Anonymous 

please try to use DAX to create a column(not in PQ).

output = 
SUMX(FILTER('Table','Table'[Owner ]=EARLIER('Table'[Owner ])&&'Table'[Filename ]=EARLIER('Table'[Filename ])),len('Table'[sharedwith])-len(SUBSTITUTE(SUBSTITUTE('Table'[sharedwith],",","")," ",""))+1)

 

[Anonymous]

Thanks @ryan_mayu  This is working for most of the files but when creating the visualization (Stacked bar chart) and keeping this Output field in values the numbers are being added up for all the dates and giving the wrong numbers .for eg: Owner in the X axis and Output in values axis, for dery@123  the value is shown in the graph as 8 instead of 4 . How do i solve this.

and also is it possible to remove the duplicates and count the Sharedwith users like for eg the same owner and same file is shared with same person on different dates it should be calculated only once

Date                        Owner                      Filename             Sharedwith                                                                             Output
27/07/2020         dery@123.com        test.docx                abc@123.com                                                                               4
24/07/2020         dery@123.com         test.docx               xyz@123.com,123@abc.com xyz@mnc.com  abc@123.com        4

24/07/2020         yarn @123.com        test1.ppt                                                                                                                       3
27/07/2020         yarn @123.com         test1.ppt              abc@123.com,zz@xyz.in zz@xyz.in                                                3

[ryan_mayu]

@Anonymous 

Since it was created as a column, when you creating chart you need to use average of value.

[Anonymous]

Thanks @ryan_mayu  , As asked before can you please alsohelp me how to remove duplicates and count the shared with users

[ryan_mayu]

@Anonymous 

I think you need to do that in PQ.

please see the sample below, dery has duplicated emails for 123@abc.com

1. replace space with comma

2. split sharedwith to rows

3. select owner, filename and shared with columns and remove duplicated.

4. use group by function to get the distinct count number

[Ashish_Mathur]

Hi,

This M code work

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Owner", type text}, {"Filename", type text}, {"Sharedwith", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Sharedwith", "Sharedwith - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column"," ",",",Replacer.ReplaceText,{"Sharedwith"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Sharedwith", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sharedwith"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sharedwith", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Owner", "Filename"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Filtered Rows" = Table.SelectRows(#"Merged Columns", each ([Sharedwith] <> "")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Merged"}, {{"Count", each Table.RowCount(_), type number}}),
    Joined = Table.Join(#"Filtered Rows", "Merged", #"Grouped Rows", "Merged"),
    #"Removed Columns" = Table.RemoveColumns(Joined,{"Sharedwith", "Index"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Duplicates", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.1", type text}, {"Merged.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Merged.1", "Owner"}, {"Merged.2", "Filename"}, {"Sharedwith - Copy", "Sharedwith"}})
in
    #"Renamed Columns"

Hope this helps.

[Anonymous]

@Ashish_Mathur  This query did not work , i am not getting correct count 

Is there any query that can be used by utilizing the Calculate or SUMX options

[Ashish_Mathur]

Hi,

Compare your expected result in the original post and the result which i have shown in my image.  Where is my result wrong?

[amitchandak]

@Anonymous , if possible split the rows using a delimiter

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

In that way, you will get a single email in each row

Otherwise you can use CONTAINSSTRING

if(CONTAINSSTRING([Sharedwith],selectvalue(Table[email]) ,1,0)

https://docs.microsoft.com/en-us/dax/containsstring-function-dax

https://docs.microsoft.com/en-us/dax/search-function-dax

https://docs.microsoft.com/en-us/dax/find-function-dax

[Anonymous]

@amitchandak  Hi, I was looking for creating a measure which can one query where to use SUMX or Calculate to get the require output 

[andre]

EDIT: forgot to use calculate in return...

sharedWithUsers = 

Var currentOwner = Tabe[owner]

var currentFilename = Table[filename]

return calculate(CONCATENATEX(values(Table[SharedWith]), Table[SharedWith], ", "), FILTER(ALL(TABLE), table[owner] = currentOwner && table[Filename] = currentFilename)))

or something like that