Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

Is there a way to collect data from two tables with a relationship into one table.

I have two table that I want to combine to create chart

Table Structure is as follows:

collect model.JPG

And I would like a collect of be able to  create another table with this:

collect Result.JPG

So basically I would like all the sales detail with the associated company. Is there a way to path this or collect this into one table?




Super User
Super User


I will have to make a few assumptions before answering your question.


  1. You datasource is a Sharepoint List
  2. Your column types are all text and number fields


Avoiding use of the LookUp function in Sharepoint makes this pretty-straightforward.  Here's the code to join both tables.


        'Sale Data',
        "Company Name",
        LookUp('Daily Reports',ReportNo = 'Sale Data'[@ReportNo])

Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Dear mdevaney,

First, I am so thankful for you taking the time to answer my novice questions.

Next, I actually tried this from from the research I did and got invalid argument on the lookup part of function ('Daily Reports',ReportNo ='Sales Data'[@ReportNo]). I also just reformatted everything to match your's exactly and got the same error.

ClearCollect(myJoinedCollection,AddColumns('Sales Data',"Company Name",LookUp('Daily Reports',ReportNo ='Sales Data'[@ReportNo])))

What you need to know is that these are CDS tables with a relationship defined on ReportNo ('Daily Reports'.ReportNo O>>>M 'Sales Data'.ReportNo). So the ReportNo in 'Sales Data' is looking up ReportNo in 'Daily Reports'

I also tried:

ClearCollect(myJoinedCollection,AddColumns('Sales Data',"Company Name",LookUp('Daily Reports',ReportNo ='Sales Data'[@ReportNo.ReportNo])))

With no success. I know it has something to do with the fact that ReportNo in 'Sales Data' is a lookup to 'Daily Reports', but at a road block as to what I am doing wrong.

Hi Jason, if Company Name is a text field on the parent entity, another option would be for you to create a calculated field on the child entity and enter the action: ReportNo.Company Name. Then you could just use that field in your report..

Thank you for your suggestion, however the 'Company Name" on the parent (Daily Report) is lookup to a different parent called 'Divisions'. I will try your idea as a work around, but my thinking is the whole idea of Relationships is to avoid these kind of work arounds. This would so much easier with an SQL statement.

you just need to compare the GUIDs of the child entity to the parent entity. try this:

in this example, EntityB is my parent, and EntityA is my child entity. EntityB 0 -->M EntityA and A has a lookup column called OfficeLookup to B. I want to add the 'Address 1' column from B to my collection

ClearCollect(colMyJoinCDS, AddColumns(EntityA, "Address", LookUp('EntityB', EntityB = EntityA[@'Office Lookup'].EntityB,'Address 1')));

The GUID column name of each entity is the name of that entity, highlighted in orange

So in your case

ClearCollect(myJoinedCollection,AddColumns('Sales Data',"Company Name",LookUp('Daily Reports','Daily Reports' ='Sales Data'[@ReportNo].'Daily Reports',ReportNo)))

 This is the only way that I have been able to get the data in my projects. but If you only want to display the data in a DataTable or a Gallery, you don't need to do all these things. just use ThisItem.'Your LookUp Column Name'.'Column Name'


Please Accept as Solution if this post answered your question so other members can find it. If you found this post helpful consider giving my post a Thumbs Up!

Ok, this all looks perfect, and I can even follow the logic, but I am still getting a "invalid arguement" error. See below screen shot. Just to clear the actual name of the "Sales Data" is "Sales Detail" in the CDS entity. I just wrote down the wrong name on the little data model I drew. 

Here is the actual screen shoot from the test app:

collect problem.JPG

you have two entities 'Sales Detail' and 'Sale Details' . I think you are confusing them, you mentioned that your sales data is in 'Sales Detail' but you are using the 'Sales Details' in your formula.

Thanks Edwin, but that was just a typo. The Entity is "Sales Details" and "Sales Data" does not exist other that the data model example i provided, which should have been labeled "Sales Details". Also still have "Invalid Arguement" on this function.


the screenshot that you had sent, had both with "s" and without "s" entities, that's why I thought you are using the wrong one. other than that I can't tell what's happening there. It has always worked fine for me this way, and I don't get the error.

Helpful resources

Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,326)