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

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.