Skip to main content

Get Linked board items and mirrored items from API

  • January 10, 2024
  • 4 replies
  • 408 views
N

I don’t think I am doing anything to complex. I have a board that I want to bring in to Power BI. The board has a few columns that are native to that board. I can get the data from those columns no problem. The issue lies when I am trying to get the data from a lookup table and a few mirror columns that pertain to the lookup. I have followed a few other posts on the forum but noting is working like it should. I have a few screen shots below to maybe help explain what I am working with.

Main Board:

image

Secondary Board:

image

Lookup field:
image

Mirror Fields:

image

Can someone point me in the right direction? Right now this is what I have for my API call in the API playground:

{
  boards(ids: [12345678910, 98765432101]) {
    items_page(limit: 100) {
      cursor
      items {
        id
        name
        column_values {
          column {
            title
          }
          text
          ... on BoardRelationValue {
            column {
            	title
          	}
            display_value
          }
          ... on MirrorValue {
            column {
              title
            }
            display_value
          }
        }
      }
    }
  }
}

As far as the Power BI side I am using This Topic as my example.

Am I on the right track?

4 replies

A
  • anon29275264
  • Participating Frequently
  • January 10, 2024

You seem to be on the right track. The only thing of course is make sure your Power BI request header includes API-Version: 2024-01 it won’t work (2024-01 is basically the same as 2023-10 for the regular API - and will be default starting the 16th of January at which point it won’t have to be specified anymore - but for testing now it does.)

You don’t need to duplicate the column{title} within the … on fragments. Anything in column_values will execute always, with whats in the fragments executing only on columns of that type.

    N
    • NEI_KGuthrie
    • Author
    • New Participant
    • January 10, 2024

    So then something must be missing on the Power BI side. Following the directions from the post I mentioned before I am getting all of the rows from the IT Vendors column, then all the rows from the Invoice column.

    image

    It SOULD be something like this…

    image

    A
    • anon29275264
    • Participating Frequently
    • January 10, 2024

    The main issue is probably that the value is returned as display_value key not the text key on mirror and board relation columns.

    So you need to populate the columns with display_value if present, otherwise text. I don’t know how to do that part, not a Power BI guy. In Javascript we’d use the ?? null coalescing operator (which I know exists in Power Query too, but not sure in scripts.

    N
    • NEI_KGuthrie
    • Author
    • New Participant
    • January 12, 2024

    I thought about that, but I don’t have a way to associate the column with the correct value in the Power Query. I think I may have to go back to the drawing board with my API call to try and nest a call for each row. So, each column value will need to look at another board to get the information. Does that make any sense?

    Terms & ConditionsCookie settingsAccessibility statement