New Table Question

[DonalMc]

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 created in the Power Query Editor with merges.

Is there an easy way to create my desired table, or do I have to create a measure per role?

Thanks! 

[vanessafvg]

if you can provide dummy date in text format i can put it together in a pbix for you.

[DonalMc]

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

[ImkeF · Accepted solution]

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.

[DonalMc]

Hi @ImkeF - thanks so much - that worked perfectly!