none
Lookup column as link between sharepoing lists RRS feed

  • Question

  • Hello,  I have 2 lists, Award and Modification.  The Award list has a column called Project Code.  The Modification list uses a lookup of that column to add modifications to an award.  I am in PBI and trying to set up a relationship but I cannot.  The project Code field in the Modification list is called ProjecrCodeid and it is a number.  I tried manually linking them but I cannot get data to show from the Modification list when I click on a specific award.  Thank you.
    Thursday, October 26, 2017 7:43 PM

Answers

  • OK, I'm assuming that's what you want.  In the PowerBI Desktop Queries Window, select the query on the Modification table. In the columns listed, you will see ProjectCodeId as a scalar (integer) column, but you will also see "Project" (or something like it) listed as a record.  If you don't see it - click on that column and select ManageColumns|ChooseColumns|ChooseColumns and make sure that column is checked.  You will then see in the upper-right of the column header a small box with two arrows in it. Click that button and you will see all the cascaded columns from the Project table. Select whatever values you'd like to have in the query (name?) and click OK. Now you will see a column called Project.Name which expands the other table property based on the join from the ProjectCodeId.

    • Marked as answer by rtwigg Friday, November 3, 2017 8:51 PM
    Friday, November 3, 2017 3:39 PM

All replies

  • Hi there. Do the Project Code/ProjectCodeId columns in each table have the same data type?

    Ehren

    Friday, October 27, 2017 11:09 PM
    Owner
  • Well, no.  As I said, the ProjectCodeID (in the Modification list) is a lookup of a text field called ProjectCode in the Award List.  In the Modification list the lookup column is called ProjectCode as well but is shows up as ProjectCodeID in PBI and is a number field.  Example:

    Award: ProjectCode = 10.1004.01.110

    Modification: ProjectCodeID = 1

    Next Award:

    Award: ProjectCode = 10.0202.02.10

    Modification: ProjectCodeID = 2

    And so on.  If I look at the modification list directly I see Project code column that is a dropdown of the Award list column ProjectCode.

    Thursday, November 2, 2017 1:02 PM
  • Ok. I'm still unclear on how these fields are supposed to relate. How would I look up a Modification based on the Award data (or vice versa)?

    Ehren

    Thursday, November 2, 2017 4:45 PM
    Owner
  • I don't see how I could be any clearer.  The Award list has a column called ProjectCode that is text.  Modification list also has a column called ProjectCode but it is a lookup of the same column in the Award list.

    When I bring both lists into PBI, the modification list shows the projectcode column as projecrcodeid and has a number that corresponds to the project code in the Award list but it is not the actual text of the project code in the award list, just a number representation of that value in the award list.

    Try is yourself, make 2 lists with one list having a lookup of the other and see what I am talking about.  It should be a one to many relationship.  One Award may have many modifications.


    • Edited by rtwigg Thursday, November 2, 2017 5:29 PM
    Thursday, November 2, 2017 5:15 PM
  • Greetings! Just to clarify the ask here...  I understand that you have a lookup key in one SharePoint list which references the parent list in a 1:many relationship. However, what you want in the PowerBI query result is the actual value of the lookup, not the index.

    Have I clearly stated your desired outcome?

    Thursday, November 2, 2017 10:57 PM
  • OK, I'm assuming that's what you want.  In the PowerBI Desktop Queries Window, select the query on the Modification table. In the columns listed, you will see ProjectCodeId as a scalar (integer) column, but you will also see "Project" (or something like it) listed as a record.  If you don't see it - click on that column and select ManageColumns|ChooseColumns|ChooseColumns and make sure that column is checked.  You will then see in the upper-right of the column header a small box with two arrows in it. Click that button and you will see all the cascaded columns from the Project table. Select whatever values you'd like to have in the query (name?) and click OK. Now you will see a column called Project.Name which expands the other table property based on the join from the ProjectCodeId.

    • Marked as answer by rtwigg Friday, November 3, 2017 8:51 PM
    Friday, November 3, 2017 3:39 PM
  • Perfect!  That is exactly what I needed.  Thank you.  I did a work around where I went to the list and did a workflow that updated that lookup column to another text column (lookup to text function in SPD-10, not SPD-13).  That worked but this is MUCH better.  Thanks again!
    • Edited by rtwigg Thursday, November 9, 2017 7:17 PM
    Friday, November 3, 2017 8:53 PM
  • Glad to help! Have a great weekend! :)
    Friday, November 3, 2017 9:49 PM
  • Hello,  I have another question that I hope you can help with.  I have a column called "Donor" that is a dropdown list of about 7 different donors.  When that column imports to PBI the data in the column simply says "List" (in gold like Record from previous question).  However, when I try your solution on that field it does not work.  Any ideas?
    Thursday, November 9, 2017 7:20 PM
  • Have you specified the field in the same way in SharePoint? I assume you have selected "Lookup" as the field type.  Also, what type is the "Donor" field in the source list? Is it a string? All other things being equal, "Donor" should work exactly the same way that "Project" worked in the first case. If it's not, there must be something different...
    Friday, November 10, 2017 8:34 PM
  • Actually, the field type was "List" with predetermined values in a dropdown.  I figured this out by exporting the data from the column.  I have two other questions if you don't mind.

    Can I put a link to a sharepoint list item in my report?  So, if someone wants to see the details of an item I can link them to it?

    Alternatively, If I cannot do that, can I display columns vertically in a table rather than horizontal?  Example:

    Column1: Value1

    Column2: Value2

    Rather than the default of:

    Column1    Column2

    Value1        Value2

    Thank you.

    Monday, November 13, 2017 4:37 PM
  • I tried using a multi-row card and that helped.  Not ideal, but it works.
    Monday, November 13, 2017 5:09 PM