#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.

You must know about this shortcut key in #PowerBI Desktop

Working with the field list on a large model in Power BI Desktop can quickly make you end up with a lot of expanded tables and you collapsing them one by one.

Don’t do that

Even though that is good if you want to improve your chances of beating your kids in Fortnite – it probably won’t – so instead do one of the following

If you want to use your mouse

Click the show/hide pane in the header of the Fields panel

This will collapse all expanded tables in the field list at once – plus if you have used the search field – it will clear that as well.

But you want to do it using the keyboard use

ALT + SHIFT + 1

This will collapse all the expanded tables as well.

Here is a link to the documentation about short cut keys in Power BI desktop – run through them – there might be some that can save you a click or two

Keyboard shortcuts in Power BI Desktop – Power BI | Microsoft Docs

Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

When you connect to a Power BI Dataset from Power BI desktop you might have noticed that you can see and use hidden measures and columns in the dataset.

But the hidden fields cannot be seen if you browse the dataset in Excel.

But that does not mean that you cannot use the fields in Excel – and here is how you can do it.

Using VBA

You can use VBA by creating a macro

The code will add the field AddressLine1 from the DImReseller dimension as a Rowfield if the active cell contains a pivotable.

Sub AddField()
    Dim pv As PivotTable
        Set pv = ActiveCell.PivotTable
        pv.CubeFields("[DimReseller].[AddressLine1]").Orientation = xlRowField
End Sub

If you want to add a measure/value to the pivotable you need to set change the Orientation property to xlDataFields

This means that we now have added two hidden fields from the dataset

Add hidden measures using OLAP Tools

You can also add hidden measures using the OLAP Tools and MDX Calculated Measure

Simply create a new calculated measure by referencing the hidden measure in the MDX

This will add a calculated Measure to the measure group you selected

And you can add that to your pivotable

Referencing hidden items using CUBE functions

Notice that you can also reference the hidden measures using CUBE functions

Simply specify the name of the measure as the member expression in this case as “[Measures].[Sales Profit]”

You can also refer to members from hidden fields using the CUBEMEMBER functions

Hope this can help you too.

Power On!

Spot the difference between Power BI Desktop and Power BI Desktop (Store Version) #PowerBI

On my computer I have 2 versions of Power BI Desktop installed – one from the Microsoft Store which is updated automatically and the downloaded version from downloads – and typically I have last month edition as my downloaded version.

But in my taskbar its impossible to tell the difference between the two.

Well we can solve that by changing the icon for the downloaded version – its not possible for the store version.

If you right click the icon in the taskbar and then right click the Power BI Desktop

You can select the properties for this App.

Now click the Change Icon

This will show you the current icon and now you can change this by clicking Browse – in my case I will select the icon for the PBIDocument

And click open – then icon will now be set to this

And when clicking OK

We will see the icon has changed for the Shortcut.

Notice that it will change immediately

But after a restart it will appear

Hope this can make your choice of Power BI Desktop versions easier for you as well.

How to connect to CDS from #PowerBI – Or where the h.. can I find the server URL

The last few month I have used CDS a few times in my solutions – and connected the data to PowerBI.

But one of the things I always search for is the Server URL

Above is the screen shot using the Common Data Service connector

And even the beta connector requires me to specify URL –

And even though the dialog says “Environment domain” – it is in fact the URL the connector wants – BUT without the https://

OBS – If you want to test the Beta connector – remember to enable TDS Endpoint under the Environments – Settings – Features

So where can I find the address

If you have access to the Admin center – Power Platform admin center (microsoft.com) – you can go into the environment and see URL.

If you do not have access to it – then open the model driven app and the URL is available in the address bar.

Hope this can help you.

Power On !

Tips to limit the number of steps in #PowerQuery – #PowerBI

When returning to some of my first queries built in Power Query, I always get a bit sad.

My old queries contain some unnecessary extra steps that makes it harder to evaluate what is happening in the query and in most cases I haven’t even bothered to rename the steps which makes the queries very hard to read for me and for others.

So, in order to improve here are some tips that can make mine and your queries shorter and easier to read.

Always Rename your Steps

Always spend time to rename your steps and avoid spaces in the step name.

Makes it easier to read and is documentation.

Especially important because the step name does not necessarily describe what is happening.

In this example I used the Remove Other columns function, and the step name refers to that function

– but what is actually happening is I am selecting specific columns – notice the formula bar – Table.SelectColumns

So, rename the step.

Check if you can avoid a column rename step

Using the interface in the Query Editor makes it very easy to get the “Renamed Columns” step but it can sometimes be avoided – For instance expanding a record column – in this example the DimGeography Column

– in this example the DimGeography Column and then double clicking the headers returned will lead into 2 steps but notice the last argument in the formula bar.

That is a list of column names that will be given and by modifying that we can do the expansion and renaming in one step.

Avoid the empty filter steps ie to not filter by (Select All)

When testing my queries, I sometimes filter the result to check the data

And then to clear it I sometimes use the (Select All) to remove the filter

This however leaves a filter step in your query where the Table.SelectRows function just is “each true”.

If you use the clear filter instead

The Filter step will be deleted

Limit the number of Rename Columns

Doing column renaming during the query steps can lead to several column renaming steps.

Try to do only 1 renaming step.

Limit the number of steps with Remove columns

Try to consolidate the column selection or deletion of columns in one step.

And remember the optional parameter in the Table.SelectColumns and Table.RemoveColumns where you can specify what should happen if a field in the column list is missing.

Don’t reorder your columns

You can change the column order in the Query editor but there is no need to do it

The columns will appear in alphabetical order in your data model anyway.

If you have a large table and can’t find the column use the Go to Column to locate it

A good bad example

Yes, this is one of my queries

This is the query after a clean-up – it does exactly the same but in fewer step and with a proper explanation in the step name.

Do you have any tips ?

This was a few tips to limit the number of steps in your query.

Let me know if you have any in the comments.

Stay queryious.