How to create a new table using columns from multiple tables?

[jdubs]

I know this is probably a repeat question as I have seen several different answers but none of them seem to solve my particular requirement.

I have 3 tables: Phone Call records, Accounts & Contacts where there are relationships from the Phone Calls to the Account and/or Contact keyed off of the Phone Number. I want to create a new table that merges the 3 so I have a complete set of data, containing the Call Time, Phone Number, Account & Contact (and a few other relevant fields.) I already have this presented in a table visual but I need to recreate it on the backend.

I tried SUMMARIZECOLUMNS but Power BI hangs on "Working on it" for hours until eventually it consumes all the memory on my desktop (12Gb)

[parry2k]

@jdubs can you share relationship diagram between these tables, also if possible, share sample data and expected output.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[jdubs]

Hi!

Yep, here is the diagram:

And sample tables and data:

Phone Calls Table

Accounts Table

Contacts Table

And I would like my expected output table to look like this:

Matched Calls Table

[Anonymous · Accepted solution]

Hi @jdubs ,

You can create a table visual as below directly to achieve it:

However, if you want to get a fact table, you can make Merge transformation for these three tables.

let
    Source = Table.NestedJoin(Phone_Calls, {"Phone Number"}, Accounts, {"Phone Number"}, "Accounts", JoinKind.LeftOuter),
    #"Expanded Accounts" = Table.ExpandTableColumn(Source, "Accounts", {"Account"}, {"Account"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Accounts", {"Phone Number"}, Contacts, {"Phone Number"}, "Contacts", JoinKind.LeftOuter),
    #"Expanded Contacts" = Table.ExpandTableColumn(#"Merged Queries", "Contacts", {"Contact"}, {"Contact"})
in
    #"Expanded Contacts"

Best Regards

Rena

[jdubs]

Thank you. I used the 'Merge query as New' function and was able to create the table I need. I didn't realize it was that simple.