Search code examples
excelcsvpowerbipowerquerydata-transform

Data set Transformation for Power BI

Actually i'm working on Power BI to make an analysis of authors publications numbers and trends. I have the data set shown in the image below. A column of authors and and another for their IDs in each cell, i'ev multiple authors at once, the same for their IDs so my question Is there a way to match each author with it's ID so i can proceed my analysis.

Thank you so much

enter image description here

Solution

  • Since you chose to provide your data as a screenshot, which cannot be copy/pasted into a table, I had to make up my own.

    • split each column into a list
    • combine the two lists into a table

    Source
    enter image description here

    M Code (Transform=>Home=>Advanced Editor)

    let
    Source = Table.FromRecords(
        {[Authors="Author A, Author B", #"Author(s) ID"="12345;67890;"],
         [Authors="Author C,Author D,Author E", #"Author(s) ID"="444123;789012;66666;"],
         [Authors="Author X, Author Y, Author Z, Author P", #"Author(s) ID"="1111;2222;3333;4444;"]}),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Authors", type text},{"Author(s) ID", type text}}),

//split each column into a List; trim the entries
    authors = List.Combine(List.Transform(#"Changed Type"[Authors], each Text.Split(Text.Trim(_),","))),
    IDs = List.Combine(List.Transform(#"Changed Type"[#"Author(s) ID"], each Text.Split(Text.Trim(_,";"),";"))),

//create new table
    result = Table.FromColumns({authors,IDs},
                type table[Authors=text, #"Author(s) ID"=text])
in
    result

    Result
    enter image description here