CODEWITHSUNDEEP
excelcsvpowerbipowerquerydata-transform
TIM_123
TIM_123

Reputation: 13

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

Upvotes: 1

Views: 158

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

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

Upvotes: 2

Related Questions