Showing posts with label parameter. Show all posts
Showing posts with label parameter. Show all posts

Using Power BI parameter to specify Excel workbook path in Power Query

This is a quick post to share something that I did just now and that worked. I tried to search for some sample but couldn't find. Maybe my search query was not that right...

Let's say you use an Excel workbook as your data source in Power BI. Than you collaborate with a team and as we know, not everyone has the same folder structure setup. This can cause a problem when trying to Refresh data in Power BI. How can we "overcome" this in a simple way?

Just create a Power BI parameter and pass it to the Power Query responsible for opening the Excel workbook.

In the following printscreen you see that I created a parameter called Excel workbook path. I set this path to where the workbook is located in my machine.


Then we can use this parameter in Power Query like this:

= Excel.Workbook(File.Contents(#"Excel workbook path"&"Contingency Drawdown.xlsx"), null, true)

I was having some difficulty trying to make the string concatenation work, that is, joining the parameter with the workbook name. Tried some variants and got it working with the string concatenation token &.

As you see the workbook name doesn't change (it's hardcoded) however the path is customizable by the parameter "Excel workbook path".


We just need to change this parameter value to point to our folder structure and we're done. Now we can make use of this parameter to open all Excel sheets (tables in Power BI) in a single shot.

Hope it helps.

References:

Power BI Introduction: Working with Parameters in Power BI Desktop —Part 4

Data binding view values to an array parameter in ASP.NET MVC

If you want the source codez, jump here.

This looks like something so easy to be done and at the same time I guess a lot of people fail the first time they try to get this working. This question at StackOverflow proves that. So to help the fellow developers out there I put together a concise ASP.NET MVC 4 sample application to show how to data bind/postback view values to an array parameter in an controller action method. I show two ways of doing this: using a traditional/full postback and an AJAX/partial postback. This way you can choose the best option for you.

Use case
User wants to be able to select 3 values ( at least 1 being mandatory Surprised smile ) in the view using dropdowns and data bind the selected value(s) to an array that will be treated inside the controller.

User Interface
While implementing the code I thought why not make the app a little bit fancier and give it some meaning and so I complemented the sample with color picker dropdowns. This is the UI:

ASP.NET MVC Array Data Binding sample app UI

Code
ASP.NET MVC needs the generated form elements to follow a certain format so that the model binder can do its job, that is, understand what you’re trying to do; otherwise when you debug the code you’ll just get a null value in the array parameter or ViewModel array property inside you controller action. This means that the model binder didn’t understand your posted data and I’m sure you thought it would. I’ve already been in this situation…

So the question is: How to make the model binder understand the view data? Let’s get to the code…

The sample’s code is well documented, so I’ll just copy/paste the important parts here. Just read the comments to understand what’s going on…

In the view side, you must create the input elements following this format:

@* Pay special attention to how we must name the form fields.
selectedColors is the name of a parameter defined in the controller action that will receive the form values. It’s necessary to suffix the name with [n], where n must be zero-based and unbroken. *@ @for (int i = 0; i < 3; i++) { @Html.DropDownList("selectedColors["+i+"]", Model.Colors, "-- Pick a Color --") }

In the controller side, you must have an action method with an array parameter named exactly like the name given to the dropdowns on the view side ( selectedColors ) like this:

/// <summary>
/// This action method works with a simple int[] array that's not part of a ViewModel.
/// </summary>
/// <param name="selectedColors">Simple int[] array</param>
/// <returns>ArrayPostResult view</returns>
[HttpPost]
public ActionResult TestArrayPost(int[] selectedColors)
{
    if (Request.IsAjaxRequest())
    {
        ViewBag.PostBack = "AJAX";

        return PartialView("ArrayPostResult", selectedColors);
    }

    ViewBag.PostBack = "Full";

    return View("ArrayPostResult", selectedColors);
}

The above code should take care of handling the full postback scenario.

To do an async/partial postback using AJAX you can use this jQuery code as an example:

$("#submit-ajax").click(function () {

    var selectedColors = [];

    // For each select HTML element/dropdown,
    // push its selected value into selectedColors
    $('select option:selected').each(function () {
        selectedColors.push($(this).val());
    });

    $.ajax({
        type: "POST",
        url: "/Home/TestArrayPost",
        dataType: "html",
        traditional: true,
        data: { selectedColors: selectedColors },
        success: function (response) {

            $("#result").html(response).fadeIn();

            // Hiding the GO back button...
            $("#result").find("#go-back").hide();

        },
        error: function (xhr, textStatus, exceptionThrown) {
            $('#result').html(exceptionThrown);
        }
    });
});

Note
If you want to postback the values to an array property that’s inside your ViewModel, for example:

public int[] SelectedColors { get; set; }

You can do this in the view side:

@for (int i = 0; i < 3; i++)
{
    @Html.DropDownListFor(m => m.SelectedColors[i], Model.Colors, "-- Pick a Color --")
} 

Then on the controller side, you’d have this:

[HttpPost]
public ActionResult TestArrayPost(ColorsModel model)
{
    if (Request.IsAjaxRequest())
    {
        ViewBag.PostBack = "AJAX";

        return PartialView("ArrayPostResult", model.SelectedColors);
    }

    ViewBag.PostBack = "Full";

    return View("ArrayPostResult", model.SelectedColors);
}

Source code
The code uses a bit of jQuery to control the UI logic and to postback the values through AJAX.

It’s hosted at GitHub and so you can navigate through it easily here.

You can download the Visual Studio 2012 solution here:

https://github.com/leniel/AspNetMvcArrayDataBinding/archive/master.zip

Hope it helps.

References
To implement this sample app I Googled some of the concepts employed like setting the background-color of select/dropdown options based on the option’s text. Pretty interesting stuff…

The following is a list of links that helped me:

Model Binding To A List
ASP.NET Wire Format for Model Binding to Arrays, Lists, Collections, Dictionaries
Drop-down Lists and ASP.NET MVC
@Html.DropDownListFor; How to set different background color for each item in DDL?
How do I convert an enum to a list in C#?