Forum Discussion
Read check marks
Hello All,
I have a data source like the following:
How I can get the following report?
or try this
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, ", ")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 resultyou 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! 🤠
19 Replies
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,
I got the following?
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! 👍🤠
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.
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
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"
