Read check marks

[koorosh]

Hello All,

I have a data source like the following:

How I can get the following report?

[Ahmedx · Accepted solution]

or try this

[Ahmedx · Accepted solution]

so you can understand what the code does, I've broken it down into several parts

// Extract field values from the record
t1 = Record.FieldValues(_),

// Convert the field values to lowercase
t2 = List.Transform(t1, Text.Lower),

// Find positions of occurrences of the letter "x" in lowercase
t3 = List.PositionOf(t2, "x", Occurrence.All),

// Retrieve column names based on positions
t4 = List.Transform(t3, (x) => Table.ColumnNames(#"Changed Type"){x}),

// Combine the retrieved column names into a comma-separated text
t5 = Text.Combine(t4, ", ")

[Ahmedx]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoAYiiK1YlWcoKwK5CEnOEqKmBCLghNIFGQkCuSLpCqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Users = _t, #"Full Control" = _t, Design = _t, Read = _t, #"View Only" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Users", type text}, {"Full Control", type text}, {"Design", type text}, {"Read", type text}, {"View Only", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Users"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
    #"Removed Columns"

[koorosh]

Thanks, Ahmad but what about when a user has multiple access? Like following image:

Please consider that is an example, in real there are many users.

[Ahmedx · Accepted solution]

or try this

[koorosh]

If you find time please explain the above code.

[Ahmedx · Accepted solution]

so you can understand what the code does, I've broken it down into several parts

// Extract field values from the record
t1 = Record.FieldValues(_),

// Convert the field values to lowercase
t2 = List.Transform(t1, Text.Lower),

// Find positions of occurrences of the letter "x" in lowercase
t3 = List.PositionOf(t2, "x", Occurrence.All),

// Retrieve column names based on positions
t4 = List.Transform(t3, (x) => Table.ColumnNames(#"Changed Type"){x}),

// Combine the retrieved column names into a comma-separated text
t5 = Text.Combine(t4, ", ")

[koorosh]

[Ahmedx]

you have incorrectly specified the name of the previous step here

[koorosh]

Thank you very much Ahmad. As you know I am curious and many questions have in my mind.
Could you please let me know the equivalent DAX formula for the above code?

[Ahmedx]

pls try this

[Ahmedx]

pls try this

[Ahmedx]

or try this

[
   t1 = Record.FieldValues(_),
   t2 =   List.Transform(t1, Text.Lower),
   t3 = List.PositionOf( t2,"x" ,Occurrence.All),
   t4 = List.Transform(t3,(x)=> Table.ColumnNames
   (#"Changed Type"){x}),
   t5= List.Select(t4, (x)=> List.ContainsAny({"Full Control","Design","Read","View Only"},{x})),
   t6 = Text.Combine(t5,", ")]
   [t6]

[Ahmedx]

pls rty this

[Daniel29195]

@koorosh 

did you try unpivoting the table  and then remove the rows where value  = blank ? 

STEP 1 : SELECT the column user

step2 :  click unpivot other columns 

step3 : filter out blank values /

result : 

you can then remove the column value if you want .

If my answer helped sort things out for you, feel free to give it a thumbs up and mark it as the solution! It makes a difference and might help someone else too. Thanks for spreading the good vibes! 👍🤠

[koorosh]

Thanks Daniel, but what about when a user has multiple access, like the following image?

Please consider that is an example, in real there are many users.

[Daniel29195]

@koorosh 

isnt this what you would like to have ?  or am i missing something ? 

if you want to merge them into one row,  then you can use DAX function concatenatex  

[koorosh]

Daniel, Please let me know how to concatenate attributes for each user.

[Daniel29195 · Accepted solution]

Hello @koorosh 

create a calculated column as follow ; 
 

cc =  
var datasource =
calculatetable ( 
tbl_name, 
allexcept(tbl_name,tbl_name[access])
)

var result =  concatenatex ( datasource , tbl_name[attribute_col_name], " , " )

return result 

you can use the same logic ( with some minor modification ) and create it as a measure ,

it would be flexible and will be dynamically modified base on your slicers and filters existing on the page .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[ThxAlot]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoAYiiK1YlWcoKwK5CEnOEqKmBCLghNIFGQkCtUvgIuGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Users = _t, #"Full Control" = _t, Design = _t, Read = _t, #"View Only" = _t]),
    #"Combined Columns" =
    let colNames = List.Skip(Table.ColumnNames(Source))
    in
        Table.CombineColumns(Source, colNames, each Text.Combine(List.Accumulate(List.PositionOf(_,"",Occurrence.All,(x,y) => x<>y), {}, (s,c) => s & {colNames{c}}), ", "), "Access")
in
    #"Combined Columns"

For fun only, a showcase of powerful Excel formulas,

[koorosh]

I got the following?