Forum Discussion

DonalMc's avatar
DonalMc
Advocate II
5 years ago
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 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! 

 

 

Need Help
  • ImkeF's avatar
    ImkeF
    5 years ago

    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.

     

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    5 years ago
    if you can provide dummy date in text format i can put it together in a pbix for you.
    • DonalMc's avatar
      DonalMc
      Advocate II
      5 years ago

      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's avatar
        ImkeF
        Community Champion
        5 years ago

        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.