Power BI – Connect to your secure API

Power BI’s web portal and desktop application are slightly different, but this tutorial will work for both.

Getting API Data

  1. Open up Power BI and select “Edit Queries”.
  2. Click “New Source” and select “Blank Query”.
  3. Next, select “Advanced Editor”.

    Capture.PNG

  4. In the editor, enter the API’s address and any header information needed for that api.
    let
         apiUrl = "https://xxxxxxx/api/info",
         options = [Headers =[#"Subscription-Key"="xxxxxxxxxxxxxxxxxxxxx"]],
         result = Web.Contents(apiUrl , options)
    in
         result
    

    Capture.PNG

  5. After you add the Query, you will then need to “Connect”. In Power BI Desktop, a warning message pops up letting you know this. Click “Edit Credentials”

    Capture.PNG

  6.  The Access Web content pop-up will show a few different ways to set up you API credentials.  For this example, we just needed to include a “Key” in the header. Since we already did that, all we have to do is press “Connect”.

 

Thats it! You should now be connected and see the results of the api call.  You can “drill” into the results and select what information and object are important to you.  As you do this, check out “Advanced Editor” again.  You will see that slight changes are made to it as you configure the data you want.

Creating your first ASP.NET Core 1.0 Web API with Swashbuckle

Using ASP.Net Core 1.0 Visual Studio 2015 I will walk through how to create a Web API. We will add some basic documentation and hook it up with Swashbuckle and Swagger. The end result is gonna be AWESOME!

Setup the Project

  1. Open up Visual Studio and create a new .Net Core.
    1.PNG
  2. Select the “Web API” template.2.PNG
  3. After the project has been created we need to tell it to export an XML document when it builds. This will allow us to generate a XML doc that will hold our swagger info and comments.
    Right Click the Project > Select “Properties” > Select “Build” > check XML documentation file > Save3.PNG

Setup Project.json and Startup.cs

  1. Open up your project.json file. This will be new to you if you haven’t played with previous releases of vNext.

    What is project.json?

    Introduced in the first versions of ASP.Net vNext (which is now called ASP.NET Core), the project.json file defines your package metadata, your project dependencies and which frameworks you want to build for. (relatively similar to ASP.NET 4x’s package.config). You can remove or add a dependencies from this file. When you do, youre solution will automatically be updated to reflect those changes. You can also set what frameworks you want your app to compile across here! Note that Microsoft has stated that project.json may go away around the time Visual Studio 15 is released. The idea with that move is that settings will move back to the .csproj file to enable better integration with MSBuild in that time frame.

  2. Since we selected the Web API template, the core API dependencies and tools were automatically added to both these files when this project was created. So all we need to do is add the Swashbuckle dependency to the project.json file.- “Swashbuckle”: “6.0.0-beta902”

    Your dependencies property should now look like this:

      "dependencies": {
        "Microsoft.NETCore.App": {
          "version": "1.0.0",
          "type": "platform"
        },
        "Microsoft.AspNetCore.Mvc": "1.0.0",
        "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
        "Microsoft.AspNetCore.Server.Kestrel": "1.0.0",
        "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
        "Microsoft.Extensions.Configuration.FileExtensions": "1.0.0",
        "Microsoft.Extensions.Configuration.Json": "1.0.0",
        "Microsoft.Extensions.Logging": "1.0.0",
        "Microsoft.Extensions.Logging.Console": "1.0.0",
        "Microsoft.Extensions.Logging.Debug": "1.0.0",
        "Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
        "Swashbuckle": "6.0.0-beta902"
      }
    
  3. Next, open up Startup.cs.  In the ConfigureServices method you will need to know the path to your xml comments file which will be automatically generated for you. This path will come from GetSwaggerXMLPath().  Make sure to add the correct xml file name, it will be named [project name].xml.  You also will need to add the swagger service and configure a few properties as shown below:
     private string GetSwaggerXMLPath()
     {
     var app = PlatformServices.Default.Application;
     return System.IO.Path.Combine(app.ApplicationBasePath, [Your Project Name].xml);
     }
    public void ConfigureServices(IServiceCollection services)
    {
                // Add framework services.
                services.AddMvc();
    
                // Swagger services.
                services.AddSwaggerGen();
                services.ConfigureSwaggerGen(options =>
                {
                    options.SingleApiVersion(new Info
                    {
                        Version = "v1",
                        Title = "Test API",
                        Description = "Jesse's Test API",
                        TermsOfService = "None",
                        Contact = new Contact() { Name = "JESSE.NET", Email = "info@test.com", Url = "www.JesseDotNet.com" }
                    });
                    options.IncludeXmlComments(GetSwaggerXMLPath());
                    options.DescribeAllEnumsAsStrings();
                });
    }
    

    You should see the new XML file in your bin folder after you Build. Build your project and check for that now. It will be named [project name].xml

  4. Lastly, add the swagger middle-ware to the Configure method like below:
    public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
    {
       app.UseMvc();
    
       app.UseSwagger();
       app.UseSwaggerUi();
    }
    

You should now be able to see your Swagger page. Run the project and navigate to http://localhost:XXXXX/swagger/ui/index.html

4.PNG

Add Swag Documentation to your API controllers!

First lets quickly go over what Swagger and Swashbuckle are:

Swagger is a simple yet powerful representation of your RESTful API. With the largest ecosystem of API tooling on the planet, thousands of developers are supporting Swagger in almost every modern programming language and deployment environment. With a Swagger-enabled API, you get interactive documentation, client SDK generation and discoverability.

-swagger.io

Swashbuckle is basically, a implementation of Swagger for ASP.NET

In addition to its Swagger generator, Swashbuckle also contains an embedded version of swagger-ui which it will automatically serve up once Swashbuckle is installed. This means you can complement your API with a slick discovery UI to assist consumers with their integration efforts. Best of all, it requires minimal coding and maintenance, allowing you to focus on building an awesome API!

-Swashbucket git repo

Now, to get our shiny new Swagger UI to show documentation of our API lets open up ValuesController.cs and add a new method to play with its comments and attributes.

/// <summary>
/// New example api endpoint that returns a int.
/// </summary>
/// <remarks>Pass in a int and this method will add 10 to it and then return it.</remarks>
/// <param name="valueA"></param>
/// <returns></returns>
[HttpGet("ExampleGet/{valueA:int}")]    //inline constraints - route matching does NOT happen if valueA is not a int with this ':int' setting.
[Produces(typeof(int))]                 //adds model and schema to swagger very helpful for passing around objects. 
[Consumes("application/json")]          //Tell this action to only accecpt json objects. Without it, it will accept anything<img width="16" height="16" class="wp-smiley emoji" draggable="false" alt=":)" src="https://s1.wp.com/wp-content/mu-plugins/wpcom-smileys/simple-smile.svg" style="height: 1em; max-height: 1em;">
public IActionResult ExampleGet(int valueA)
{
    try
    {
        valueA = valueA + 10;
        return this.Ok(valueA);
    }
    catch (Exception)
    {
        return this.BadRequest("Fail!");
    }
}

After adding the above method our page will look like this:
5.PNG

and if we try to pass it a non integer into this new endpoint we will get this:
6.PNG

Finished! Congrats!

There is still much to learn, I’d recommend checking out the dotNetConf videos for more .Net Core info.  Also is should be noted that our project above just used the default response formatting which is JSON. The response  data can be configured to things like XML by setting formatters in Startup.cs