Using Field Parameters when connecting to a #PowerBI dataset or Azure Analysis Services

The new preview feature “Field parameters” in Power BI desktop has opened a lot of creativity in the Power BI community.

But if you use a connection to a Power BI dataset or Azure Analysis Services – the Parameters button will be greyed out

And if you don’t have the permission to change the model you won’t be able to use the feature.

So how can we use the Field parameter filter to make field selection or measure selection dynamically?

Well – we can use another preview feature to enable this

If you have turned “DirectQuery for PBI datasets and AS” on – you will get the option make changes to this model

Clicking this will change your connection to a direct query source instead of a live connection.

When we click add a local model – we can choose the tables and measures we want to include

And our storage mode will change to “DirectQuery”

But another thing changes as well – the New parameter – Fields will be enabled on the modelling tab

And then we can start building our Field paramaters

In this case for selecting what to appear on the X-axis and another one for selecting Measures / values

And then we can make our visuals dynamic by using our field parameters in the X-axis and Y-axis in the visual

When publishing the report, it will mean that the workspace will get an extra dataset

But don’t worry the linage is visible and as it is a direct query there is no need for setting up datarefresh

If you connect a new report to the dataset you will also get the field parameters in the new report as the information is stored in the direct query dataset.

Hope you find this useful and if you do – give this post a like please

#PowerQuery – Create a date column from Year number and a month name

Sometimes it’s the little things that can help you minimize the number of steps in your Power Queries.

Here is another example that might help you.

Let’s imagine your data contains two columns with year and the month name and you want to create a date column.

Start by selecting both columns and under the Add Column tab select Merge Columns

Specify the separator as a space and give your column a name (to avoid a rename column step )

This will result in a step

Now modify the step to

And then in one step you have a date column – as I have a Danish format in the format is DD-MM-YYYY.

Happy Querying

#PowerAutomate – Get a copy of an invoice from your E-conomic system

In my company we use e-conomic as our ERP system and it is also used to send invoices to our customers.

Now our commercial managers do not have access to e-conomic but they do need the information about what is invoiced to our customers.

We have build our own CRM system using a Model driven Power App where each commercial manager has access to his accounts.

So how can I give the account manager access to a specific invoice for a specific customer ?

Power Automate and the e-conomic api comes to the rescue.

First of all you need to sign up for a developer account at e-conomic – Developer (e-conomic.com) – this will give you the opportunity to build an app in e-conomic that you can install in your e-conomic ERP app.

There is a fine description on how to do this here – Connecting to the APIs using tokens (e-conomic.com)

When the app is installed you will get an AppSecretToken and a Granttoken – the App secret is generated when you install the app and the Granttoken is visible in the list columen “Adgangs-ID”

Set up the flow

  1. Go to https://make.powerautomate.com/
  2. Pick the right environment
  3. And choose create
  4. Then I picked “Instant cloud flow

    But you could pick another type of trigger from one of the other connectors – perhaps react to an e-mail with the invoice number as a subject or ???

  5. Name your flow and choose the trigger
    and click create
  6. And in order to retrieve a single invoice I add an Input field
  7. Click the Next step button and search for the HTTP connector

    and choose the HTTP action

  8. Choose the GET method and for the URI – you specify – https://restapi.e-conomic.com/invoices/booked/

    and then select Invoice number from the dynamic content list of fields

    and after the Invoice number you add – /pdf – !!!

  9. In order to authenticate the Http request against the e-conomic API – we have to add some headersThe headers has to be X-AppSecretToken and X-AgreementGrantToken

    and you have to use the information from your App registration to get these.

  10. The http request will return a pdf document that then should be saved somewhere – when search for “create file” you can see a lot of possible places to save it

    In this example I will save the file to a OneDrive for Business folder

    So I choose a folder and name the file with the dynamic content of the invoice number and use the Body returned by the Http step as the content of the file that should be created.

  11. Next up – lets save the flow and test it

    so I enter a invoice number I know and hit run

    And success

Final words

In our case we have enhanced the process a bit more and transfer the invoices to an Azure blob storage on a daily schedule plus we have integrated our model driven app with Power BI reporting. So the customer form contains a tab with a Power BI report filtered by the customer with access to all the invoices and KPI’s for the customer.

Hope this give you some inspiration as well.

#PowerBI Convert all Power BI links in Power Point to images

Yesterday I posted an image of some PowerPoint VBA code on LinkedIn and Twitter and a lot of people have asked for a copy of the code.

So I have made a Power Point file with the code included and a small instruction on how to use it.

The code has been changed compared to the posted image as it ran to fast for Power Point – so I added a pause in the code to wait 1 second for each conversion.

The Code

Sub ConvertAllPBIToImages()
Dim x As Slide
Dim shp As Shape

Dim sShapes As Shapes

'Loop through all the slides
For Each x In ActivePresentation.Slides

    x.Select
    'Loop through all the shapes on the slide
    For Each shp In x.Shapes

        'Is it a Power BI App - Report
        If shp.Title = "Microsoft Power BI" Then

            'Inserted to make sure as the code sometimes runs
            'a bit to fast for Power Point
            WAIT = Timer
            While Timer < WAIT + 2
               DoEvents  'do nothing
            Wend

            shp.Copy

            Set sShapes = x.Shapes

            x.Shapes.PasteSpecial ppPasteBitmap

            shp.Delete

        End If
    Next
Next

End Sub

Here is a link to a file with the code.

LINK

Hope you find it useful.

#PowerQuery – Convert a referenced query column to a table without Table.FromList() – Magic trick

This week I watched the video from Guy in A cube with Patrick and Alex Powers – You thought DAX? Alex shows you Dynamic M-M-Magic! – YouTube.

And one trick I noticed Alex used was something I didn’t know about Power Query and had annoyed me for a long time.

As you might have seen in my previous posts I like to make the queries as short as possible – and this tip will help me do that even better than my previous tip – How to avoid a rename step in #PowerQuery when you use Table.FromList – #PowerBI – Erik Svensen – Blog about Power BI, Power Apps, Power Query (wordpress.com)

Let’s imagine I have a query with a column called Reportid – and I want to create another query with the unique values from the column.

Normally I would create a new blank query and than reference the column with – queryName[ColumnName]

And then we have a new query that contains a list.

I want a table, so I use the “To Table” in the ribbon or the shortcut menu

Click Ok in the dialog

And a new step is added and I end up with a table

THE TRICK –

Instead use double square brackets and you will not only get a table, but the column name is also as I want it. – Query[[ColumnName]]

That is awesome – hope you find it useful too

Happy Querying!!!

#PowerQuery – Add Year, Month and Day to your date table with Date.ToRecord – #PowerBI

When you build a date table in Power Query you might use the functions under Date to add year, month and day

And this will give you three steps in your Query

But we can do this a bit faster, and you will save a few clicks with your mouse

If you add a custom column using this formula

= Table.AddColumn(#”Changed Type”, “Custom”, each Date.ToRecord([Date]))

You will get a column containing a record

Then next step is to expand the record

And Voila – Year, month and day in 2 steps instead of 3.

Update – 25/10-2021

If you want to specify the datatype for each of the Year, Month and Day you can modify the formula for addColumn to

= Table.AddColumn(#”Changed Type”, “Custom”, each Date.ToRecord([Column1])
, type [ Year = number, Month = number, Day = number]
)

This will make the columns numbers

Happy Querying

How to avoid a rename step in #PowerQuery when you use Table.FromList – #PowerBI

Here is a tip that reduces your Power Query steps with 1 step less.

When I build date tables I typically use the function List.Dates to create a list of dates.

As I want it as a table I use the Ribbon interface to convert the list of dates into a table

Which then gives med a table with on column called Column1

Instead of renaming the column using the interface like this

And thereby getting an extra step “Renamed Columns”

You should modify the third argument in the Table.FromList and specify the name of the column in a list

= Table.FromList(Source, Splitter.SplitByNothing(), {“Date”}, null, ExtraValues.Error)

Which will reduce the number of steps with 1.

Multivalue parameters in #PowerBI paginated report when using #PowerAutomate to export to file

I have had a couple of people posting comment on one of previous blogposts “Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate” asking me on how to specify values for multi value parameters in a Paginated reports.

So, I decided to write a blog post on how you can do it – if you have solved in other ways than my solution please let me know in the comments

How to do it

In my example I have created a simple report that has one multi value parameter that is called “StoreChain” and the parameter label is Chain.

And in the Power BI Report Builder the Report parameter is setup to Allow multiple values

Now let’s create the flow

I will create a flow where the user can type in the values for the Chains that the report should be filtered by – So I choose to create an instant cloud using the trigger “Manually trigger a flow”

I add an Text input called Chain and add a description to the input field

When we then select the Action “Export To File for Paginated Reports” – we can select the workspace and report we want to run

At the bottom of the action dialog we have the ParameterValues name and value section

But if we specify the values by referring to the trigger input – we will get a string value containing all the chains separated by ; (semicolon)

For example running with this

Will give us

And because there is no chain called this we will get an error when we try to test the flow

Solution

We need to specify the parameter values a little bit different

If you click on the button “Switch to input entire array” we can specify the Parameter values as an array instead

And this reveals that in order to specify more than one chain we need to specify an array of values containing a column called name and value – like this.

This means that before we call the action to export the paginated report we need to build an array of the specified chains (parameter values).

First I create an array variable called pv containing an empty array

Next, I use an “Apply to each” where I use the split function to create an array of the text specified in the trigger.

And inside the Apply to each step I build the parameter value array by using the action “Append to array variable” where I construct an item in the pv array for each of the result of the split function

Then I can use the pv Array variable to Export To File for Paginated Reports – ParameterValues setting

And then to finish the flow we can add a “Send an email (V2)” action to send the report

The final flow will have the following steps.

Next – let us test it

With this entered as text for the input

The steps will specify the ParameterValues as an array

And after about 35 seconds the flow has emailed me the requested reports

Success πŸ™‚

Hope you find this useful.

Using Calculation Groups in #PowerBI to implement a Many 2 Many (M2M) filter

Yesterday I tweeted about how I solved the implementation of a Many 2 Many filter using Calculation groups.

And was encouraged to write a blog post about this – so here it is 😊.

The scenario

Imagine a simple star schema where we have a fact table with sales and a customer and Date dimension.

And a in the demo I have also created a few measures – (in my actual customer case I had over 100 measures)

Now we want to expand the model to handle that a customer can belong to one or more customer groups –

This is done by adding the table Customer Groups that contains the Customer Groups and then a bridge table that contains the link between the customers to the different customers groups.

For instance, Customer Key 12031 both belongs to Customer Group 1 and 2.

But when we try to report on the Sales Value the value for the groups will be identical

This is because row context comes from the table Customer Groups and because the filter from that table can’t be passed to the customer table as the cross filter direction points the other way (red circle)

We could change the cross filter direction to Both via the Edit relationship dialog

And it would solve the problem.

But as Alberto Ferrari highlights in this blog post – Bidirectional relationships and ambiguity in DAX – SQLBI – this can be dangerous.

Change all the measures

For all our measures to work I would have to update them all and add a CROSSFILTER statement each of the measure statement

This will give me the result I want but also some work to update all measures.

Use Calculation groups to add filter to all your measures

Instead let’s create a Calculation Group to apply the filter – and the tool to do this is the Tabular Editor – Tabular Editor

We will add a Calculation Group via Tables

And add a group called “Customer Group Filter”

Next step is to add a Calculation Item

I will call it “Apply Customer Group Filter”

And then in the expression editor I will add the expression that applies the CROSSFILTER to the SELECTEDMEASURE().

Clicking Save and returning to Power BI Desktop will prompt me to refresh the calculation groups

Now I will have a Calculation Group Filter in my Fields list

And we can add a slicer or a filter to the visual, page or all pages

And if we select the Calculation Item we can see the filter is being applied.

You can download the demo file – here

Summary

Typically, the calculation groups examples are normally focused on Time intelligence but absolutely not limited to time – I hope this example can give you some inspiration to use Calculation Groups in other scenarios as well.

In my actual case I saved time on updating over 100 measures – so I really ❀ calculation groups.

Let me know what you think and if you find it useful as well.

#PowerBI – Change the data source in your composite model with direct query to AS/ Power BI Dataset

I have been playing around with the new awesome (preview) feature in the December Power BI Desktop release where we can use DirectQuery for Power BI datasets and Azure Analysis services (link to blogpost)

In my case I combined data from a Power BI dataset, Azure Analysis Services, and a local Excel sheet. The DirectQuery sources was in a test environment.

I then wanted to try this on the actual production datasets and wanted to change the datasources – and was a bit lost on how to do that but luckily found a way that I want to share with you.

Change the source

First you click on Data source settings under Transform data

This will open the dialog for Data source settings and show you the list of Data sources in the current file.

Now you can either right click the data source you want to change

Or click the button “Change Source…”

Depending on your data source different dialogs will appear

This one for my Azure Analysis Services Connection

And this one for Power BI Dataset

And this one for the Local Excel workbook

Hope this can help you to.

Happy new year to you all.