cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerAnalyst Visitor
Visitor

Adding a new column with a loop

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:

 

YearPublication typePublication Forum levelAuthor 1Author 2Author 3Author 4Author 5
2018 TYPE A 3AB   
2018 TYPE B 2CDEAW
2017 TYPE B 1BWA  
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 NameResearch Group
AGROUP 1
BGROUP 1
DGROUP 2
EGROUP 3
FGROUP 4
GGROUP 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.

1 REPLY 1
Super User
Super User

Re: Adding a new column with a loop

I would start with unpivoting the 1st table (in PowerQuery) to have the authors in rows rather than columns
Then I would suggest to use a 2nd table as a basis - and count the nr of unique publications that share the authours between two tables