Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

API call iterating through a column/list

Hi maybe I am just stupid, but I try to solve this since hours. So here is what I am doing:  I query a list that is called allrepos (already available as a function but I am stuck)  Allrepos gives...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi TeigeGao  thanks for your answer. 

    Sorry that I just post now.

    = Table.AddColumn(Quelle, "Ratings", each Json.Document(Web.Contents("http://80.158.34.203:3000/api/v1/repositories/"& [uid] & "/trends/ratings", [Headers=[Authorization="Bearer xxxxxxxxxxxxxxxxxxxxxxxx"]])))

    I resolved the issue myself a few minutes ago. (Morning coffee wonders, yeah!) The problem was that my call did not comply with the https://docs.microsoft.com/de-de/powerquery-m/web-contents documentation. RTFM to myself! 

    The Header part of the query was missing, so it did not understand was the Authorization part was and the error message was a bit misleading.  

    Then it worked like a charm. 

    The next problem I ran into was after expanding the table that it did not have a unique primary key anymore. 

    The trick is to ignore the error and create an n-to-1 connection in the data modeling view.

    Really start to like power bi.

    Low Code BI wonder :D 

     

    Thanks for your detailed help, I really appreciate it.

     

     

TeigeGao's avatar
TeigeGao
Solution Sage
7 years ago

Hi Anonymous ,

The error message "Expression.Error:The Value ""authorization=Bearer..."" cannot be converted into the type "Record". " indicates that the uid column cannot be added to the string directly.

Actually, if we want to use variable in a table to a string, we cna use the following method:

First, we can create a function like below:

(id as text) =>
let
    Source = Csv.Document(File.Contents("C:\Users\teigeg\Desktop\" & id & ".csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}})
in
    #"Changed Type"

In your scenario, it should be, you can get a query from one Json file import then add the (uid as text) and & [uid] & to it.

(uid as text)= 
let Source = Json.Document(Web.Contents("http://80.158.34.203:3000/api/v1/repositories/"& [uid] & "/trends/ratings", "authorization = Bearer xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")))
in 
Source

Then go to your variable table, click 'Invoke custom function' like below:

Then expand the table:

Best Regards,

Teige

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi TeigeGao  thanks for your answer. 

Sorry that I just post now.

= Table.AddColumn(Quelle, "Ratings", each Json.Document(Web.Contents("http://80.158.34.203:3000/api/v1/repositories/"& [uid] & "/trends/ratings", [Headers=[Authorization="Bearer xxxxxxxxxxxxxxxxxxxxxxxx"]])))

I resolved the issue myself a few minutes ago. (Morning coffee wonders, yeah!) The problem was that my call did not comply with the https://docs.microsoft.com/de-de/powerquery-m/web-contents documentation. RTFM to myself! 

The Header part of the query was missing, so it did not understand was the Authorization part was and the error message was a bit misleading.  

Then it worked like a charm. 

The next problem I ran into was after expanding the table that it did not have a unique primary key anymore. 

The trick is to ignore the error and create an n-to-1 connection in the data modeling view.

Really start to like power bi.

Low Code BI wonder :D 

 

Thanks for your detailed help, I really appreciate it.