#PowerBI – Copy a measure in the desktop – Why it doesn’t always work – mystery solved

Well – creating measures in the Power BI desktop is usually doing a lot of copying and pasting of measures.

And unfortunately, we haven’t yet gotten a Copy measure in the shortcut menu when we right click a measure

Here is an idea you can vote for if you would find it useful as well – https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13219620-duplicate-measure-and-format-copy

So we end up copying the formula from text in the formula bar

And click new measure and Paste it into the formula bar

 

But 8 of 10 times nothing is pasted (at least when I select) – WHY ???

 

And now I finally took me the time to find out why –

Notice the colour of the selection in the picture above compare to this

If the colour is darker blue you can copy and paste but if its lighter blue – you can’t – and this is properly because you use your mouse to select the formula and if you release the mouse outside of the formula bar area – the selection will be light blue and when you then press CTRL + C to copy the formula text isn’t copied to the clipboard.

Solution

So, either make sure you don’t move your pointer outside the area or use CTRL + A to select all and then press CTRL + C to copy the formula.

 

Hope this can save you a couple of clicks and please vote for the idea – https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13219620-duplicate-measure-and-format-copy – this also includes the idea that the formatting of the value should be included.

 

Power On!

 

 

 

 

 

#PowerBI – A quicker way to set Summarize to none for all your fields in your date dimension (or others)

When designing your model you will in most cases need a date dimension and one of the things you always should do is set the Summarization of the number columns in your table to none instead of the default Sum – to avoid this when you insert for instance Calendar year in your visuals

So set the summarization to Don’t summarize to avoid this.

But you have to do this for all the number columns in your date table – –

but if you use the Model view instead –

We can multi select columns and set it for all at once

In the model view we have to select None to avoid summarization of number fields – and voila all fields in my date table is set to don’t summarize

Hope this can help you as well.

Quick #PowerQuery trick – Get duration days between two dates

Just a quick tip that you might not be aware of in the Power Query Editor.

If you select two columns in the view and on the Add Column tab, select the Date button – you can select to Subtract Days

This will give you the number of days between the dates in the selected columns

Use the formula bar to rename the new column by modifying the step

 

Hope this can help you too –

#PowerQuery – Calculate the ISO date from year and a week number

Just wanted to share a M function that I just created to calculate the date from a Year and a ISO week number.

The example data is the following

Now the function I created can be called with the Year and Week number as parameters to get the following result

The function has the following code and will return the date of the Monday of the week number.

(TheYear as number, TheWeek as number) as date =>
let
//test
//TheYear = 2018,
//TheWeek = 1,
//
offsetToISO = Date.AddDays(#date(TheYear,1,1),-4),
dayOfWeek = Date.DayOfWeek(offsetToISO),
offset = -dayOfWeek + (TheWeek * 7),
isoWeekDate = Date.AddDays(offsetToISO, offset)
in
isoWeekDate

Hope this can help you too.

Here is a link to an example pbix file – link

#PowerQuery – Control the expand columns so it includes new columns

Image a scenario where your column in your PowerQuery that contains a table with a set a columns that you know at some point will have more columns added.

In the case above I know that we at some point will add more columns in the merged ProductAttributes table.

How can we make this dynamic using PowerQuery

When we click the icon for expanding the table, we might just select this and move on

But notice the formula created in

It says

= Table.ExpandTableColumn(#”Merged Queries”, “ProductAttributes”, {“Brand”}, {“Brand”})

This means that even though we might add new columns to the ProductsAttributes table – it will still only be Brand that is expanded and only that column.

The bolded arguments is 2 lists that contains the Column names to expand and the new names of the columns – the last argument is optional so we can actually skip that if we want the original names – https://docs.microsoft.com/en-us/powerquery-m/table-expandtablecolumn

Now by changing the formula to this

= Table.ExpandTableColumn(#”Merged Queries”, “ProductAttributes”,List.RemoveItems(Table.ColumnNames(#”Merged Queries”[ProductAttributes]{0}), {“ProductKey”})
)

We can make the table dynamically expand when adding new columns in the table ProductAttributes

We get the new column included as well

The magic formula does this

Table.ColumnNames(#”Merged Queries”[ProductAttributes]{0})

Will return a list of column names from the step before
expansion (note I use the step name and column name) – and I use the {0} to extract the column names only form the first row – otherwise the formula will fail.

But as we cannot have the same column names twice (i.e. ProductKey needs to go away) so we need to use the List.RemoveItems functions

List.RemoveItems(Table.ColumnNames(#”Merged Queries”[ProductAttributes]{0}), {“ProductKey”})

Thereby removing the ProductKey Item in the list

And this means that when we get more columns in the table “ProductAttributes” table they will automatically be included in the expanded columns

Hope this can help you power queries even more dynamic.

Here is an example file – Link

Power Query On !

Adding keyboard shortcuts to your favourite buttons in #powerbi

If you find yourself clicking the same button in the Power BI Desktop you might find this little tip useful.

When you right click a button in the ribbon – you can add it to the Quick Access Toolbar.

This will add the button at the top (unless you have positioned it below the ribbon)

The button in the quick access toolbar can now be activated using a shortcut key combination.

When you press ALT the buttons will be labelled with numbers

So, pressing ALT + 7 will I my case open the Query Editor –

You can use the same technique in the Power Query Editor so by pressing ALT + 2 in my Query Editor will open the Advanced Editor

Hope this can help you too and do things even faster in Power BI with fewer clicks.

#powerbi Report to browse and watch sessions from #mbas 2019 using the #powerapp visual

Unfortunately I wasn’t able to participate in the Microsoft Business Application Summit this year – but luckily we can watch all the session via https://community.powerbi.com/t5/MBAS-Gallery/bd-p/MBAS_Gallery

But that would mean I had to leave Power BI Desktop in order to search and watch the videos – and the website also made it hard to see the distribution of sessions between categories.

So, I created this –

And to watch the video from within PowerBI I created a drill through page where I used the PowerApp visual to be able to show the

As none of the Microsoft standard visuals can play videos from within a report – I created a power App to show the video based on the selected video link.

If you want to embed this huge resource of learning material in your own tenant you can download the elements from here

The Desktop file – link

The Power App – link

If you are interested in learning how I scraped the web page for all the relevant data – check out these functions to extract data from pages using CSS query capabilities in the power query function Html.Table

Highly inspired by this blog post by Chris Webb – https://blog.crossjoin.co.uk/2018/08/30/power-bi-extract-urls-web-page/