cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PANDAmonium
Resolver III
Resolver III

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

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!

2 ACCEPTED SOLUTIONS
lbendlin
Super User III
Super User III

Did you maybe forget to specify the GroupID ?

View solution in original post

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!

View solution in original post

4 REPLIES 4
lbendlin
Super User III
Super User III

"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.

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!

View solution in original post

lbendlin
Super User III
Super User III

Did you maybe forget to specify the GroupID ?

View solution in original post

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors