Forum Discussion
Solved
Reference a column from another table in Power Query Editor new column function/query
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:...
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.
Any chance you could paste the data in a table instead of screenshots?
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 |