Skip to main content

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?

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.

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

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.

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?

Reply


Terms & ConditionsCookie settings