Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DonalMc
Advocate II
Advocate II

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! 

 

DonalMc_0-1601560619080.png

 

1 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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
vanessafvg
Super User
Super User

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




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.