Power BI creating multiple columns of nearly identical data (SharePoint table source issue)

[NeilTownsend76]

I would really appreciate if someone in the community could point me in the right direction. I am trying to transform some data. The source is an online SharePoint list. On import in Power BI I select the “2.0 implementation”. One of the columns that I transform gets brought in as a table. When I expand it - I choose ‘Title’ only and it displays a persons name within the company (lets say Joe Smith”. If the record in Sharepoint has a 2nd name assigned (lets say Jane Smith) - Power BI adds an additional, nearly identical row with the exception of displaying Jane Smith’s name. Concatenating this data into a text string feels like it will not work as it cannot be used within the data model. Any suggestions on how to avoid Power BI creating all these extra rows?

[Anonymous]

Can't you just choose to expand multiple values to list, and then split the list into new columns?
Alternatively, before you expand the Title Column, you can add a RowCount column, like each Table.RowCount([Title])

Then another column

if RowCount > 1 then [Title]{1} else null

And then you can add final column

each [Title]{0}

--Nate

[NeilTownsend76]

Thanks so much for the reply. I hope I explained it correctly. This is what they SharePoint data looks like – and it uses a lookup to find names within the organization. Both pieces of data (in this case names) are important – the issue is the creating a second, nearly identical record in Power BI after you expand the table. I am not sure if your responses solve this? 

[lbendlin]

Well, as I said - I would not see it as an issue, but rather as reality.

Whenever you are trying to flatten hierarchical data like JSON or your lookup column you need to make decisions. Either live with the "duplicates" or destroy information.

[lbendlin]

You can choose to

- not include these lookup columns  in you SharePoint List extract (they are very costly)

- only expand the first row  ( "{0}"  ) of these records

- expand the entire record because that's what the actual data is