Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
So, I need to connect to a web service sending my login/password as authentication and a query requesting what I need.
Example:
Web service will receive:
login
password
query
And will return:
JSON file.
How can I do that using Power BI?
I tried using parameters, but it didn't work.
Thanks.
Solved! Go to Solution.
This is a function that takes a token as a mandatory parameter and a path as an optional one to fetch data from dropbox for example. If this doesn't help, we would need more details about where your exact problems lie:
let Source= (token as text, optional folder as text) => let data = [ path= if folder = null then "" else folder, recursive=false, include_media_info=false, include_deleted=false, include_has_explicit_shared_members=false ], header = [ #"Authorization"="Bearer "&token, #"Content-Type"= "application/json"], response = Web.Contents("https://api.dropboxapi.com/2/files/list_folder",[Content=Json.FromValue(data),Headers=header]), out = Json.Document(response,1252), entries = out[entries], ToTable = Table.FromList(entries, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ExpandProperties = Table.ExpandRecordColumn(ToTable, "Column1", {".tag", "name", "path_lower", "path_display", "id", "client_modified", "server_modified", "rev", "size", "content_hash"}, {".tag", "name", "path_lower", "path_display", "id", "client_modified", "server_modified", "rev", "size", "content_hash"}), RetrieveContent = Table.AddColumn(ExpandProperties, "File", each Web.Contents("https://content.dropboxapi.com/2/files/download",[ Headers=[#"Dropbox-API-Arg"="{""path"":"""&[path_display]&"""}", #"Authorization" = header[Authorization]]])) in RetrieveContent ,documentation = [ Documentation.Name = " fnDropbox.Folder ", Documentation.Description = " Returns a table with contents from your selected Dropbox folder " , Documentation.LongDescription = " Returns a table with contents from your selected Dropbox folder. The optional field 'folder' allows you to access sub-folders within the main folder. ", Documentation.Category = " Accessing data functions ", Documentation.Source = " local ", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com ", Documentation.Examples = {[Description = " " , Code = " Check this blogpost explaining how it works: http://wp.me/p6lgsG-AA ", Result = " "]}] in Value.ReplaceType(Source, Value.ReplaceMetadata(Value.Type(Source), documentation))
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
This is a function that takes a token as a mandatory parameter and a path as an optional one to fetch data from dropbox for example. If this doesn't help, we would need more details about where your exact problems lie:
let Source= (token as text, optional folder as text) => let data = [ path= if folder = null then "" else folder, recursive=false, include_media_info=false, include_deleted=false, include_has_explicit_shared_members=false ], header = [ #"Authorization"="Bearer "&token, #"Content-Type"= "application/json"], response = Web.Contents("https://api.dropboxapi.com/2/files/list_folder",[Content=Json.FromValue(data),Headers=header]), out = Json.Document(response,1252), entries = out[entries], ToTable = Table.FromList(entries, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ExpandProperties = Table.ExpandRecordColumn(ToTable, "Column1", {".tag", "name", "path_lower", "path_display", "id", "client_modified", "server_modified", "rev", "size", "content_hash"}, {".tag", "name", "path_lower", "path_display", "id", "client_modified", "server_modified", "rev", "size", "content_hash"}), RetrieveContent = Table.AddColumn(ExpandProperties, "File", each Web.Contents("https://content.dropboxapi.com/2/files/download",[ Headers=[#"Dropbox-API-Arg"="{""path"":"""&[path_display]&"""}", #"Authorization" = header[Authorization]]])) in RetrieveContent ,documentation = [ Documentation.Name = " fnDropbox.Folder ", Documentation.Description = " Returns a table with contents from your selected Dropbox folder " , Documentation.LongDescription = " Returns a table with contents from your selected Dropbox folder. The optional field 'folder' allows you to access sub-folders within the main folder. ", Documentation.Category = " Accessing data functions ", Documentation.Source = " local ", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com ", Documentation.Examples = {[Description = " " , Code = " Check this blogpost explaining how it works: http://wp.me/p6lgsG-AA ", Result = " "]}] in Value.ReplaceType(Source, Value.ReplaceMetadata(Value.Type(Source), documentation))
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF.
Could you please convert your code to use as web query?
once I can list/view all files inside a folder, I can handle it myself.
using web query, I am having issue to make it work with my token and the link below.
Not sure if I understand your request, but this is the query-version of it:
//(token as text, optional folder as text) => let token = <<Fill in your Token here>>, folder = null, data = [ path= if folder = null then "" else folder, recursive=false, include_media_info=false, include_deleted=false, include_has_explicit_shared_members=false ], header = [ #"Authorization"="Bearer "&token, #"Content-Type"= "application/json"], response = Web.Contents("https://api.dropboxapi.com/2/files/list_folder",[Content=Json.FromValue(data),Headers=header]), out = Json.Document(response,1252), entries = out[entries], ToTable = Table.FromList(entries, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ExpandProperties = Table.ExpandRecordColumn(ToTable, "Column1", {".tag", "name", "path_lower", "path_display", "id", "client_modified", "server_modified", "rev", "size", "content_hash"}, {".tag", "name", "path_lower", "path_display", "id", "client_modified", "server_modified", "rev", "size", "content_hash"}), RetrieveContent = Table.AddColumn(ExpandProperties, "File", each Web.Contents("https://content.dropboxapi.com/2/files/download",[ Headers=[#"Dropbox-API-Arg"="{""path"":"""&[path_display]&"""}", #"Authorization" = header[Authorization]]])) in RetrieveContent
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I came across one of issue in powerbi . It would be great if you can suggest / help me accordingly. I am trying to write R / Python script in PowerBi to send the selected fields / value in Slicer to a local web service and once web service will get data from power Bi , it will send a response which should be displayed / Visulaize in Power Bi. FYI the response is 0 or 1 depending on the value selected. It should work like - Displaying response on the fly .
hum, no.
web query I mean using the web connector wizard, as in the picture.
second picture is just an example, language pt-br.
Sorry, I've never used that, so no help here.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I'll try to solve this way.
Thx.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |