Dataflow and Rest API - We cannot convert a value of type Table to type Text

[PANDAmonium]

Hi All,

I'm trying to use the Power BI REST API and store some of the results in a dataflow. I got it working in Power BI Desktop, but I wanted to store some of the higher level objects data in a dataflow: groups, reports, datasets. These wouldn't need to be refreshed as often, probably just a manual refresh will do.

Everything works perfectly in Power BI Desktop, but when moving the code into a dataflow I get an error. I can get the results if I'm running just one call that's not in a table. The issue is when I add a column to run the REST call against each Group Id in a table, it'll say:

Expression.Error: We cannot convert a value of type Table to type Text.

Details

Value = #table({"Value"}, {})

My guess was that it's trying to auto convert the results to text mid step. I've tried going into the Project options and Global options and turning off Auto detect column types, but for whatever reason it just turns back on next time I open the project.

Here's what my function looks like for the REST call:

let

  __AccessToken = #"Access Token",

  GetResult = (__GroupId) => 

        Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/" & __GroupId & "/reports",

            [

                Headers = [#"Authorization"=__AccessToken]

            ]

        )

in

  GetResult

Then I'm just adding a column to my Group table and passing it along the Group Id to the function. The results in each cell say binary, but it gives that error when you select a cell or try to go onto the next step. This is the part that I'm stuck on, but the rest of the query goes on to format as json, format as list, format as record, and expand so I'm not trying to apply before converting it to text.

Thanks in advance!

[lbendlin · Accepted solution]

Did you maybe forget to specify the GroupID ?

[lbendlin]

"I've tried going into the Project options and Global options and turning off Auto detect column types, but for whatever reason it just turns back on next time I open the project."

I have seen similar issues where dataflow insists on adding column type conversion steps although I explicitly asked it not to do that.  I think that is either a very bad design decision (given that dataflows at the end of the day are just glorified CSV files)  or a bug. Might be worth raising as an issue.

[PANDAmonium · Accepted solution]

You were probably right about the missing Group Id 😑  I changed a few things since my last attempt including the API permissions. I had initally enabled Group.Read instead of Group.ReadAll accidatally, so it probably was either not returning anything when calling those group ids or not getting the ids just during validation...but did get all the ids for my Group table on refresh 😕

I still don't understand why it was still pulling all the groups in the dataflow and everything else was working correctly in Postman and Power BI Desktop though. The scope and authentication are the same in the token request, so you would think the issue would arise sooner but for some reason it was only on dataflow validation for objects within groups that that it occured.

Ran it twice though and is seems to be working. Thanks for the help!

[lbendlin · Accepted solution]

Did you maybe forget to specify the GroupID ?

[PANDAmonium]

No, I didn't. It worked in Power BI Desktop, and I edited and tested multiple versions of it attempting to get it to work including filtering it down to just one Group Id. I can get the access token and the groups. The dataflow just doesn't like it when I try doing the same thing programatically into a new column.