Forum Discussion

jdubs's avatar
jdubs
Helper V
6 years ago
Solved

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

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)

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
      jdubs
      Helper V
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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