Match Multiple Values (Comma Delimited) Power BI

Question

I'm working with this set of data, ( articles ID (EID) & authors) I would like to match authors or their ID with each ID article .

Example : author 1 : article 1 , article 8 ...

I've no idea how to do this in both M or DAX. P.S : I tried to split the authors column and unpivot but it didn't work.

Answer 1

Power query has good tools to accomplish this. I don't see a good way to split out the "Authors with affiliations" column in your dataset, so this answer will give you the relationship between the "Author(s) ID" and "EID":

1. Right click on "Author(s) ID" column and select Split Column > By Delimiter
2. Set the delimiter to Semicolon and split at "Each occurrence of the delimiter"
3. In the advanced options, select split into rows

This will create a row for each author ID and EID combination. The "Authors with affiliations" column will have duplicate values.