The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
107 | |
76 | |
61 |
User | Count |
---|---|
276 | |
129 | |
122 | |
100 | |
88 |