Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
NeilTownsend76
Regular Visitor

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

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?

4 REPLIES 4
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
NeilTownsend76
Regular Visitor

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? 

NeilTownsend76_0-1728398505854.png

 

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
Super User
Super User

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.