How to aggregate two data sources into one Data table?

(0) Share

Report

Posted on by dlprentice

628

I have 2 SharePoint lists, 1 camelCase column can create parity between the two.

SharePoint List #1 = 'ESN Ticket Tracker' (Contains most of what I want)

SharePoint List #2 = 'Networking Device List Master' (Contains the rest of what I want such as City, and State)

How do I aggregate these together using a Data table?

Currently I am applying some filtering as seen by the screenshots. If this can somehow be done in SharePoint at the source or with Power Automate I am open to that as well.

Screenshot 2021-02-03 105947.png Screenshot 2021-02-03 105940.png

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

eka24 20,923 on at

Like (1)

Report
Copy link

Link copied!
You can on the items property of the Datatable put:

AddColumns( 'ESN Ticket Tracker', "city2", LookUp( 'Networking Device List Master' , camelCase = 'ESN Ticket Tracker'[@camelCase], city ), "State2", LookUp( 'Networking Device List Master' , camelCase = 'ESN Ticket Tracker'[@camelCase], state ) )

Or

create a collection OnVisible or OnStart of the App as :

ClearCollect(MyCombinedTable, AddColumns( 'ESN Ticket Tracker', "city2", LookUp( 'Networking Device List Master' , camelCase = 'ESN Ticket Tracker'[@camelCase], city ), "State2", LookUp( 'Networking Device List Master' , camelCase = 'ESN Ticket Tracker'[@camelCase], state ) ))

Then use the Collection, MyCombinedTable on the Items property of the DataTable

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
dlprentice 628 on at

Like (1)

Report
Copy link

Link copied!
THANK YOU

I had to tweak things a little, but this is working 🙂

For example 'DNS Entity Name' is the name of the column on SharePoint List 2 that I'd like to match up with 'Service ID' on the current data table.

Final code:

AddColumns( 'ESN Ticket Tracker', "City", LookUp( 'Networking Device List Master', 'DNS Entity Name' = 'ESN Ticket Tracker'[@'Service ID'], City ), "State", LookUp( 'Networking Device List Master', 'DNS Entity Name' = 'ESN Ticket Tracker'[@'Service ID'], State ) )

One last bit of help if you don't mind...
How do incorporate the following in the above code?
Filter('ESN Ticket Tracker',Status=StatusDropdown.SelectedText.Value)

Was this reply helpful? Yes No
Verified answer

eka24 20,923 on at

Like (1)

Report
Copy link

Link copied!

Use this;

Filter (

AddColumns( 'ESN Ticket Tracker', "City", LookUp( 'Networking Device List Master', 'DNS Entity Name' = 'ESN Ticket Tracker'[@'Service ID'], City ), "State", LookUp( 'Networking Device List Master', 'DNS Entity Name' = 'ESN Ticket Tracker'[@'Service ID'], State ) )

,Status=StatusDropdown.SelectedText.Value)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
dlprentice 628 on at

Like (0)

Report
Copy link

Link copied!

YOU ROCK!

Was this reply helpful? Yes No