Forum Discussion
How to add content to cells programmatically
Hello,
I'm sorry if this is a rather obvious question, but I am trying to learn how to add cell content programmatically. The particular goal I'm trying to achieve is to look through a table and add content to a cell if it fulfills certain criteria. In my example, I have a list of companies and contacts who are attending an event. Some companies have no staff members attending the event at all, but other companies have at least one staff member who is attending the event.
I'd like to be able to see all the companies who have no staff members attending at all and not also include staff members who are from a company where someone else is attending. To do this, I'd need to look through the table and find every contact who is attending and for each staff member that is attending, find their company and mark all contacts from that company as attending. Alternatively, for each staff member that is attending, remove all instances of their company within the table. This is a fairly simple programming task, but I'm new to Power Bi and thought I would ask if there was an easy solution to this that I'm missing.
I've posted a screenshot of some arbitrary data which shows some people attending and others in the same company not attending. I'd like to apply the 'YES' to all those who are at the same company. The end goal would be to then have a clean list of companies who have no staff members attending at all.
I'd appreciate any ideas! Thanks.
2 Replies
Hello,
You should be able to do this with PowerQuery.
- Duplicate your original query
- Remove all columns except "Company" and "Attending"
- Filter "Attending" Column for "YES"
- On Company Name Column remove duplicates
This will leave you with a table containing the company names that have an employee attending and you can then merge this table back with the original on the company name column.
Depending on the join type selected you could get back only those companies that are not on the list of companies that have an employee attending (Using one of the ANTI join types)
Here is a DAX solution which will give you a list of Companies who had no one attending at all
Table 2 = VAR T1 = SELECTCOLUMNS(FILTER('Sheet1','Sheet1'[Attending]="YES"),"Company Name",[Company Name]) VAR T2 = SUMMARIZE('Sheet1',Sheet1[Company Name]) RETURN EXCEPT(T2,T1)
