Hello

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 Ex : author 1 : article 1 , article 8 ...

I'ev 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
I'll thankful for any tips or suggestions

Thanks in advance

CodePudding user response：

Is either of these what you are trying to do?

In powerquery/M:

right click Author ID column, split column by delimiter, semicolon, each occurrence, advanced options, split into rows. Then filter out the blank rows and remove extra column

Middle Version:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Author(s) ID", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Author(s) ID"),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([#"Author(s) ID"] <> "")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Authors with affiliates"})
in  #"Removed Columns"

or, Right version:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Author(s) ID", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Author(s) ID"),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([#"Author(s) ID"] <> "")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Authors with affiliates"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Author(s) ID"}, {{"EIDs", each Text.Combine(List.Transform([EID], Text.From), ","), type text}})
in #"Grouped Rows"

CodePudding user response：

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.