Reference a column from another table in Power Query Editor new column function/query

[Andries_Benade]

I want to add a column that counts the number of items that has the foreign ID in one of the corresponding row ID in another table using Power Query Editor and a formula. The formula I improvised is: List.Count(List.Select([Device.ID],[Device.ZoneID]=[ID]))

It just throws out an error as I expected since the Device.ID and Device.ZoneID columns aren't found. How do I accomplish this?

[KNP · Accepted solution]

Thanks for the data. That makes it much easier.

I'd probably tackle it with a merge.

See attached PBIX file. 

Let me know if you have questions.

[KNP]

Any chance you could paste the data in a table instead of screenshots?

[Andries_Benade]

Device

ID	Device Name	Category ID	Zone ID	Status	Is Active	Date Installe
D01	Front-door Camera	Camera	Terrazzo	Active	TRUE	1/25/2020
D02	Boiler Temp	Temperature Sensor	Boilermaker Room	Inactive	TRUE	2/2/2021
D03	Speed Testing	Speed Sensor	Safety Office	Active	TRUE	12/11/2021
D04	Entry Access	Access Control	Stucco Mason Building	Active	TRUE	3/21/2022
D05	Exit Access	Access Control	Stucco Mason Building	Inactive	FALSE	3/21/2022
D06	Light Activation	Motion Sensor	Labor Office	Inactive	FALSE	5/17/2021
D07	Front-door Camera	Camera	Environmental Office	Active	TRUE	8/1/2021
D08	Aircon Activation	Motion Sensor	Tile Setting Bench	Inactive	FALSE	8/26/2021
D09	Assembly Temp	Temperature Sensor	Linemen Assembly	Inactive	FALSE	1/23/2022

Zone

ID	ZoneName	ZoneDescription
Z01	Terrazzo	13-175 - Ice Rinks
Z02	Boilermaker Room	2-870 - Sculpture/Ornamental
Z03	Safety Office	17-030 - Bond
Z04	Stucco Mason Building	2-750 - Concrete Pads and Walks
Z05	Iron Workshop	3-400 - Precast Concrete
Z06	Labor Office	1-523 - Sanitary Facilities
Z07	Environmental Office	2-370 - Erosion and Sedimentation Control
Z08	Tile Setting Bench	1-570 - Temporary Controls
Z09	Linemen Assembly	2-825 - Wood Fences and Gates

[KNP]

Can you please provide some sample (mock-up) data from each of the two tables?

It'll be much easier to help with something to work with.

[Andries_Benade]

I made a new query/function that seems to better express what I want to do (Device Count)

[Andries_Benade]

Device

Zone