Forum Discussion
Solved
New Table Question
Folks, I have the following tables and I would like to know how to generate 'Table:The one that I want'. The bottom three tables are pulled from the CDS, and the Table: Users and Roles is one I cre...
Hi DonalMc ,
if you duplicate the column User in the "User and Roles" table and pivot on User, you'll get the desired result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQotTi1ScAQygvJzUoGMWJ1oJSMMcSewuDGGuAtY3AQm7gQTdwaLm2KIu4LFzWDizqjqzTHEIeZbYIhDzLGEibugqjc0wJAAaogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User-Role-ID" = _t, User = _t, Role = _t]), #"Removed Columns" = Table.RemoveColumns(Source,{"User-Role-ID"}), #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "User", "User - Copy"), #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Role]), "Role", "User", each if List.Count(_) = 1 then "Y" else "N") in #"Pivoted Column"oh yes: and remove the ID-column before.
if you can provide dummy date in text format i can put it together in a pbix for you.
Hi vanessafvg
See user-roels.xlsx for the xlsx I took the screencap of - is this enough?
I simplfied things to make it easier to explain.
Thanks in advance,
Donal
Hi DonalMc ,
if you duplicate the column User in the "User and Roles" table and pivot on User, you'll get the desired result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQotTi1ScAQygvJzUoGMWJ1oJSMMcSewuDGGuAtY3AQm7gQTdwaLm2KIu4LFzWDizqjqzTHEIeZbYIhDzLGEibugqjc0wJAAaogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User-Role-ID" = _t, User = _t, Role = _t]), #"Removed Columns" = Table.RemoveColumns(Source,{"User-Role-ID"}), #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "User", "User - Copy"), #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Role]), "Role", "User", each if List.Count(_) = 1 then "Y" else "N") in #"Pivoted Column"oh yes: and remove the ID-column before.
