Hello. I'm quite new to Power BI and I couldn't describe the problem well in the title so let me explain it here.
I'm handling data about journal publications. I have a query 1 which has the following data:
| Year | Publication type | Publication Forum level | Author 1 | Author 2 | Author 3 | Author 4 | Author 5 |
| 2018 | TYPE A | 3 | A | B | | | |
| 2018 | TYPE B | 2 | C | D | E | A | W |
| 2017 | TYPE B | 1 | B | W | A | | |
| 2017 | TYPE C | 0 | | | | | |
A publication can have between 1-7 authors.
I also have a query 2 which contains the name of the author and the research group they belong to:
| Author Name | Research Group |
| A | GROUP 1 |
| B | GROUP 1 |
| D | GROUP 2 |
| E | GROUP 3 |
| F | GROUP 4 |
| G | GROUP 2 |
Now what I need to do is to find out how many publications each research group has done. The way I would approach this is to add a new column to the first query, which tells which publication has been made by which research group. The problem is that first query also contains names that are not from our department as some of the publications are done in collaborations from other research units. If the first name is not within the query 2 (the one with the names), then I would check values Author 2. Author 3 etc. until I find a name which is found from query 2.
Also to point out that most of the publications are done by authors within same research group. The above is just an example of the data.
My options are:
1) Add the column "research group" and values manually my going thru each line by comparing the values from both tables.
2) Create an IF statement which would compare the two queries and loop thru the "Author fields" until it finds a match and adds the corresponding "research group" value.
Option 1 is doable as I have around 200 rows, but from learning perspective it would be great if could do the option 2. Any suggestions?
Thanks in advance.
