Forum Discussion

mrsid2201's avatar
mrsid2201
Regular Visitor
7 years ago

Distinct column values from SharePoint Lookup

Hi,

 

I'm pretty new to Power BI. 

I have created a project status dashboard in Power BI Desktop. My data is being pulled from a Sharepoint online list. 

My online list columns are as follows:

 

Country | Vertical | Categories | Project Title | Project Stage | IT Owner | Status

 

The IT Owner column is an AD lookup field so as to easily enter a person's name. 

On my dashboard, I have a stacked column chart visualization for Project Status by IT Owner.

In some cases, there might be several IT Owners for the same project.

 

I have attached two screenshots for example:

 

  • This is the sharepoint list with two projects where the same IT owner (Bino Balan) is part of both the projects (I wasn't able to capture the status column in this screenshot). 

 

  • This is the visualization for IT Owner Project Status.

 

For the above example, the visualization treats the same IT owner (Bino Balan) as two different values. How can I pull distinct names from a group of IT owners in this example and add the status count to their individual names?

 

Thanks!

General Comment
Need Help
Show and Tell
Tips and Tricks

2 Replies

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    7 years ago

    For a project including 3 people i'd say you need to create 2 additional rows. This can be done by a combination of split column and unpivot:

    Starting tableSplit column by ";"

    Highlight your new columns and then press unpivot. 

    You should get a row for each participant in each project.

    • mrsid2201's avatar
      mrsid2201
      Regular Visitor
      7 years ago

      I was able to split the columns but unpivoting the new columns caused the new columns to disappear and the following error:

       

      DataSource.Error: We couldn't parse OData response result. Error: A value without a type name was found and no expected type is available. When the model is specified, each value in the payload must have a type which can be either specified in the payload, explicitly by the caller or implicitly inferred from the parent value.
      Details:
      DataSourceKind=SharePoint
      DataSourcePath=https://#######/sites/it