How to connect the same value to multiple rows from the same table

[Hannes_Corbett]

Hi,

Currently trying to setup a PowerBI report from a document storage system I've got.

So in one table I've got the almost all the data such as

VM_DOCUMENT (table name)

• Author ID
• Owner ID
• Approver ID
• 2nd Approver ID
• Document Name
• Document Code

And in another table I've got the Role ID that gives what the Role is called and their name

VM_ROLE (table name)

• Role ID
• Role Name
• First Name
• Last Name

I want to be able to get so I can get the ones in VM_DOCUMENT to each show the VM_ROLE in different places.

For example, I want to be able to have a row with the Document Name, Author Name, Owner Name, Approver Name and 2nd Approver Name.

But I can only manage to do a connection to one of the things listed in VM_DOCUMENT and not one that is seperate to each one.

My guess is that I've got to create new table in some way but unsure how to do this.

Please let me know if you need more information or have a solution.

Kind regards,

Hannes

[lbendlin]

Feels like at least you need a Persons Dimension table, a Roles Dimension table, and a Role Assignments fact table.

[Claude_Xu · Accepted solution]

Hi Hannes,

Not sure if I understand your question clearly. Providing some sample data and output will be helpful.

If you just need information like FirstName and LastName from VM_ROLE  to be 'merged' into Author, Owner, Approver and 2nd Approver of VM_DOCUMENT, one dummy solution is to do table join multiple times. One trick here is to rename the VM_ROLE table by prefixing it because duplicate named columns are not allowed in the join result. 

For demonstration purpose, I use Excel but it should be almost identical to the case of Power BI. Suppose we have below data

Use below M code

let
DocumentTable = Excel.CurrentWorkbook(){[Name="VM_DOCUMENT"]}[Content],
RoleTable = Excel.CurrentWorkbook(){[Name="VM_ROLE"]}[Content],
// Popualte Full Name
RoleTableWithFullName = Table.AddColumn(RoleTable, "FullName", each [FirstName] & " " & [LastName]),
// Prefix for Author join
AuthorRoleTable = Table.PrefixColumns(RoleTableWithFullName, "Author"),
#"Join Author Name" = Table.Join(DocumentTable, "AuthorID", AuthorRoleTable, "Author.RoleID", JoinKind.Inner),
// Prefix for Owner join
OwnerRoleTable = Table.PrefixColumns(RoleTableWithFullName, "Owner"),
#"Join Owner Name" = Table.Join(#"Join Author Name", "OwnerID", OwnerRoleTable, "Owner.RoleID", JoinKind.Inner),
// Prefix for Approver
ApproverRoleTable = Table.PrefixColumns(RoleTableWithFullName, "Approver"),
#"Join Approver Name" = Table.Join(#"Join Owner Name", "ApproverID", ApproverRoleTable, "Approver.RoleID", JoinKind.Inner),
// Prefix for 2ndApprover
SecondApproverRoleTable = Table.PrefixColumns(RoleTableWithFullName, "2ndApprover"),
#"Join 2nd Approver Name" = Table.Join(#"Join Approver Name", "2ndApproverID", SecondApproverRoleTable, "2ndApprover.RoleID", JoinKind.Inner),
#"Select Desired Columns" = Table.SelectColumns(#"Join 2nd Approver Name", {"DocumentName", "DocumentCode", "Author.FullName", "Owner.FullName", "Approver.FullName", "2ndApprover.FullName"})
in
#"Select Desired Columns"

we can get below result

Obviously, there are duplicate lines of code to do the join operation for Author, Owner, Approver and 2ndApprover. A better way to handle that is to extract the duplicate code and form a function in M. But anyway, I think you get the basic idea here.