Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Any chance you could paste the data in a table instead of screenshots?
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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 |
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.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
I made a new query/function that seems to better express what I want to do (Device Count)
Device
Zone
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
28 | |
26 |
User | Count |
---|---|
97 | |
88 | |
59 | |
43 | |
40 |