Forum Discussion
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: 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?
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.
6 Replies
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.
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.
Device
Zone
I made a new query/function that seems to better express what I want to do (Device Count)
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
