Forum Discussion

mbrawn's avatar
mbrawn
Frequent Visitor
9 years ago
Solved

Power BI & Dynamics CRM - Merge Columns

I'm using Dynamics CRM as my data source and creating a report of Won Opportunities. In CRM there is a pre-determined list of products (Products Table) and there is also the ability to use 'written-in' products (Opportunity Products Table). I need to be able to create a column/table which just contains the contents of both of these tables as, for some reason, the relationship between the two will not allow me to show them together.

 

Products[name]

Opportunityproducts[description]

 

I don't think I can't just use merge or append as the two source tables contain different columns and when I tried this it just never completes.

 

Any help appreciated.

Thanks

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    If Append in Power Query is not working, you could try a UNION DAX function like:

     

    MyTable = UNION(DISTINCT(Sales[ItemDesc]),DISTINCT(Tickets[status]))

    This would be a New Table formula.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    If Append in Power Query is not working, you could try a UNION DAX function like:

     

    MyTable = UNION(DISTINCT(Sales[ItemDesc]),DISTINCT(Tickets[status]))

    This would be a New Table formula.

    • mbrawn's avatar
      mbrawn
      Frequent Visitor
      9 years ago

      Perfect thank you Greg_Deckler!

       

      Now I need to work out how to link this to the existing tables so I can use the column in my report but that's a separate issue for me to work out. 

       

      Thanks again