How to try/test another #PowerBI theme on your existing reports in the service

I am a big fan of using themes in Power BI but the current theme designer in Power BI desktop is very limited and doesn’t support all the things you really can do with themes.

So I use the excellent theme designer tool from powerbi.tips – https://themes.powerbi.tips/themes/palette

And I use the paid version so I can save my themes that I use for different clients.

The tool also has the excellent option of showing demo visuals to get a feeling of how it will look.

But I want to see how my new theme might look on some old report where I might have used another theme or perhaps some reports other users have created where they have done some individual formatting instead of using the theme.

And did you know – we can do it and see the result for existing report in your workspaces – and we can directly modify the theme and see if we should change a color or a font size or a….

Here is how

The Microsoft Power BI Embedded playground – Power BI Playground – gives us the option to try the developer sandbox

When you sign in to the playground with your credentials and click “Select Report” under use my own Power BI report

You will then be able to select an existing report in your Power BI tenant that you want to work with.

This will open the report in the playground and give us the opportunity to play around with the different report APIs that is available.

In this case I opened a report that I want to try and change the theme on.

Under the Layout group there is an item called “Set report theme”.

If you drag that into the code area

A code snippet with a custom theme will be added.

This is short theme file you see from row 95 to 101.

And when you click on Run

You will be able to see the awesome look of the new theme

This quickly reveals that the current theme file contains some drastic changes and we can start playing around with other color by changing the code.

Here the primary color is changed to a dark gray.

As you noticed the theme only has about 6 lines and very few settings so why not try the theme I have created in Theme generator from PowerBi.tips – so I download the file to my desktop and open it in

visual studio code.

And then I copy all the text from the editor and replace the existing theme

With the new theme code – be careful when you replace the text – you should have a ; (semicolon) at the end

Now let’s see how it looks.

And as you can see, I still need to do some work in the theme file to make it look pretty

This hopefully gives you an idea of how you can test your theme file directly on existing reports without having to download the pbix and import theme file and revert back and forth.

During my playing around with the theme generator and the playground I have come up with a few ideas that I will provide to the guys at PowerBI.tips and I am sure that they are interested in feedback from you as well on how they can improve their tool.

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.

My session at Power BI Global Virtual Conference – #PowerQuery

Last month I had the pleasure of presenting a session at the free Power BI Global Virtual Conference.

The conference was arranged by Avi Singh – PowerBIPro – link to his YouTube Channel here

My session was about Power Query and how to make your Power Queries more dynamic.

You can download my presentation and demo files via this link – here.

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

 

 

 

 

 

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

Extraction of number (or text) from a column with both text and number – #PowerQuery #PowerBI

When you are working with data in Excel or PowerBI the data often contains columns that is a combination of text and numbers.

One example could be like this

If you have this challenge you shouldn’t use Split Columns or Text.Range to do this but check out

Text.Select

Documentation here

And Chris Webb has good example using it for text – here.

My example demonstrates how to work with text but also works with numbers and capitals letters and symbols etc.

Here is how we can extract the House number and Zip Code – use the Custom Column from the Add Tab in the Query Editor window

= Table.AddColumn(Source, “Housenumber”, each Text.Select([Street], {“0”..”9″}))

= Table.AddColumn(#”Added Custom”, “Zip Code”, each Text.Select([Zip], {“0”..”9″}))

And now we have

And one other benefit is that the Function doesn’t return an error when there is no number in the string.

Here is an example file

Hope you find this useful

#PowerQuery – Filter a table based on another table column or list – and some Filter aha’s

One of my favorite features in Excel and Power BI is Power Query / M – and I just wanted to share a small trick when you want to filter a table on specific items.

Let’s imagine you want to filter a list of customers based on different CustomerCategoryID’s –

Using the interface, you would select the different categories via the filter menu

If you select 3, 4 and 5 – you will get this filter

= Table.SelectRows(Sales_Customers, each ([CustomerCategoryID] <> 6 and [CustomerCategoryID] <> 7))

Notice that it M creates an expression that excludes 6 and 7 and not specifically selects the 3, 4 and 5 – this means that when new customer categories is created they will be included in your query as well – perhaps not what you intended!!

If you only select 3 and 4 the expression built will be

= Table.SelectRows(Sales_Customers, each ([CustomerCategoryID] = 3 or [CustomerCategoryID] = 4))

So, it seems that if you pick more than half it will build and expression with and <> statement instead of and equal statement.

To make sure that only categories that you want to include or exclude you can use a list to specify the keys to be included

To create a list you can use this expression to

= {3..5} – will give you values from 3 to 5

Or

= {3,6,5} – will give you 3, 6 and 5

To filter your table, you now need to modify the Table.SelectRows expression

= Table.SelectRows(Sales_Customers, each ([CustomerCategoryID] = 3 or [CustomerCategoryID] = 4))

To

= Table.SelectRows(Sales_Customers, each List.Contains(Query1, [CustomerCategoryID]))

The List.Contains will check whether each row in the table will have a CustomerCategoryID number that exists in the list and return true if it does and your table will then only contains rows where True is returned

If you wanted to exclude the values that you have in your list you can change the expression to

= Table.SelectRows(Sales_Customers, each List.Contains(Query1, [CustomerCategoryID]) = false

Happy Querying !

And here is an example file – Link

I am now a MVP :-)

Last week I received an e-mail from Microsoft that I had been awarded the MVP Award.

This made me glad and proud.

My colleagues, friends and family all know me as a very passionate and loyal Microsoft fan – and now I can show that it has been noticed from Microsoft as well

I want to say thanks for all the people that has attended my talks during the years, people that have allowed me to talk and the great community of SQL Saturday, MS BIP Denmark and finally the Power BI Community.

I will continue my work for especially the Power BI community and have set a few goals for the coming year – one them is of course to get the MVP Award for 2019-2020.

A special thanks to Ruth Pozuelo for nominating me.

 

 

 

How to build a location aware #PowerApp – ‘Guide book Copenhagen’ – #opendatadk and #powerquery

In one of my latest projects we have used PowerApps to create a location aware selection of stores – and I wanted to share my experience about how to do this.

So, I found an open data set about attractions, restaurants, hotels and much more in Copenhagen.

https://portal.opendata.dk/dataset/guidedanmark-oplevelser-overnantning-aktiviteter-i-hele-danmark

In order to get the data into PowerApps – I created an Excel workbook and used PowerQuery to import the data in a Table.

The Query to create the table is quite simple and contains a little renaming and removal of unwanted columns, and I only imported the rows that has an Latitude and Longitude.

let

Source = Json.Document(Web.Contents(“https://portal.opendata.dk/dataset/44ecd686-5cb5-40f2-8e3f-b5e3607a55ef/resource/23425a7f-cc94-4e7e-8c73-acae88bf1333/download/guidedenmarkcphenjson.json&#8221;)),

#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“Id”, “Created”, “CreatedBy”, “Modified”, “ModifiedBy”, “Serialized”, “Online”, “Language”, “Name”, “CanonicalUrl”, “Owner”, “Category”, “MainCategory”, “Address”, “ContactInformation”, “Descriptions”, “Files”, “SocialMediaLinks”, “BookingLinks”, “ExternalLinks”, “MetaTags”, “RelatedProducts”, “Places”, “MediaChannels”, “Distances”, “Priority”, “Periods”, “PeriodsLink”, “PriceGroups”, “PriceGroupsLink”, “Routes”, “Rooms”, “Capacity”}, {“Id”, “Created”, “CreatedBy”, “Modified”, “ModifiedBy”, “Serialized”, “Online”, “Language”, “Name”, “CanonicalUrl”, “Owner”, “Category”, “MainCategory”, “Address”, “ContactInformation”, “Descriptions”, “Files”, “SocialMediaLinks”, “BookingLinks”, “ExternalLinks”, “MetaTags”, “RelatedProducts”, “Places”, “MediaChannels”, “Distances”, “Priority”, “Periods”, “PeriodsLink”, “PriceGroups”, “PriceGroupsLink”, “Routes”, “Rooms”, “Capacity”}),

#”Expanded Address” = Table.ExpandRecordColumn(#”Expanded Column1″, “Address”, {“AddressLine1”, “AddressLine2”, “PostalCode”, “City”, “Municipality”, “Region”, “GeoCoordinate”}, {“AddressLine1”, “AddressLine2”, “PostalCode”, “City”, “Municipality”, “Region”, “GeoCoordinate”}),

#”Expanded GeoCoordinate” = Table.ExpandRecordColumn(#”Expanded Address”, “GeoCoordinate”, {“Latitude”, “Longitude”}, {“Latitude”, “Longitude”}),

#”Filtered Rows” = Table.SelectRows(#”Expanded GeoCoordinate”, each ([Latitude] null and [Latitude] 0)),

#”Removed Columns” = Table.RemoveColumns(#”Filtered Rows”,{“Municipality”, “Region”, “ContactInformation”, “Descriptions”, “Files”, “SocialMediaLinks”, “BookingLinks”, “ExternalLinks”, “MetaTags”, “RelatedProducts”, “Places”, “MediaChannels”, “Distances”, “Priority”, “Periods”, “PeriodsLink”, “PriceGroups”, “PriceGroupsLink”, “Routes”, “Rooms”, “Capacity”}),

#”Expanded Category” = Table.ExpandRecordColumn(#”Removed Columns”, “Category”, {“Name”}, {“Name.1”}),

#”Removed Columns1″ = Table.RemoveColumns(#”Expanded Category”,{“Owner”}),

#”Expanded MainCategory” = Table.ExpandRecordColumn(#”Removed Columns1″, “MainCategory”, {“Name”}, {“Name.2”}),

#”Renamed Columns” = Table.RenameColumns(#”Expanded MainCategory”,{{“Name.2”, “MainCategory”}}),

#”Removed Columns2″ = Table.RemoveColumns(#”Renamed Columns”,{“AddressLine2”}),

#”Renamed Columns1″ = Table.RenameColumns(#”Removed Columns2″,{{“Name.1”, “Category”}}),

#”Removed Columns3″ = Table.RemoveColumns(#”Renamed Columns1″,{“Created”, “CreatedBy”, “Modified”, “ModifiedBy”, “Serialized”, “Online”, “Language”})

in

#”Removed Columns3″

The Excel file is then saved in Onedrive for business.

 

Lets build the app

 

I use the Web studio.

 

And select the Blank app with a Phone layout

On the canvas I click the Connect to data and create a new connection that connects to Onedrive for Business and pick the Excel file

 

So now we have a connection to data in our App

 

And I insert the following controls

 

The first two labels show the my location as latitude and longitude, and the I inserted a slider with a min and max of 0 to 2000 as the radius in meters around my location. The label above my slider is just to show the selected radius.

Now we can insert a drop down and set the Items to the data connection and the column Name in that data connection and see it works.

 

Now we must filter the items based on our current location. In order to do this, we must filter our items. This can be done using the FILTER function.

The formula the uses the slider to modify the radius around our location

Filter(CopenhagenGuide, Value(Latitude, “en-US”) >= Location.Latitude – Degrees(Slider1/1000/6371) && Value(Latitude, “en-US”) = Value(Location.Longitude, “en-US”) – Degrees(Slider1/1000/6371/Cos(Radians(Location.Latitude))) && Value(Longitude,”en-US”) <= Location.Longitude + Degrees(Slider1/1000/6371/Cos(Radians(Location.Latitude))) )

And if I now limit the radius to 173 meters you can see I have 4 places nearby

If you want to add a map as well highlighting the selected Attraction you can do that as well

 

You can find the information to do that here – https://powerapps.microsoft.com/en-us/blog/image-control-static-maps-api/

 

If you want a copy of the PowerApp file you are welcome to add a comment or ping me on twitter @donsvensen and I will send it to you.

 

Hope you can use this – Power ON!

 

 

 

 

How to change the #PowerBI desktop file connection from data model to a Power BI Service dataset or #AzureAS

In April 2017 we got the ability to connect our Power BI Reports to datasets in the service (link) and that is really cool.

Today I got a question from a colleague on how to change a reports dataset in order to separate the reports from the data model – thereby having a pbix file with data model and then design reports by connecting to the dataset in the Power BI service and I came up with this workaround.

This technique can also be used if you have reports that you want to change the connection of a report to an Azure Analysis Services or copying a report to another workspace and modify the connection to a dataset in that report.

Let’s see how we can do this

In this example I have designed a data model and report that is connected to data in a SQL database

And deployed the pbix file to the service

This gives us a dataset and a report.

Now if we open the report and chose to export as pbix

Now I named the report – Demo PBI Exported.pbix

This will include the data model including all the queries etc.

If we deploy the this to the service again we will end up with 2 datasets – not a good idea – we will end up with two datamodels 😦

The best thing would be that this report was tied to the dataset in the service – but the “Get Data” doesn’t give us the option to change the connection in a file with a data model

 

So, I decided to create another pbix file with a connection to the Power BI

This gives me the same fields but is connected to the service

Notice that you can’t see the data and relationship in the panel to the left

 

So now I have 2 pbix files – one connected to the service and one with the report and the datamodel (and the original model)

First thing is to change the 2 files extension to zip – as the pbix files is just a zip file with different files within.

Now the exported pbix files listed in the picture to the right has a “large” datamodel as it includes the data and queries.

I then extracted both zip files to separate folders

I then copied the 2 files

Connections

Datamashup

From the pbix file connected to the service

And pasted them into the folder containing the extracted pbix file Exported from the service

I choose to overwrite the existing

Deleted the file called datamodel and zip all the files in the container to a new zip file – in this case called magic

Now change the extension to pbix and opened the file.

This will give us this look of our reports

Not exactly Magic you might say – but wait – It actually is – now go the get data and connect to the Power BI Service

 

And voila your report is now connected to the Power BI service

 

When publishing this to the service you won’t get another dataset but a new report connected to the dataset.

 

And even better when/if users download the report as a pbix file its connected to the service and not the data model.

Using this method also enables you to copy reports between workspaces and just point to the correct power bi service dataset.

 

Can it be done if we want to change connection to Azure Analysis Services – It sure can 🙂

 

So, I uploaded the pbix file to Azure Analysis Services to create a copy of the data model in Azure using the web-designer.

 

I then repeated the steps of overwriting the Connections and Mashup files in the extracted zip folder with the files from the extracted service folder.

And created a new zip file – Magic Azure AS.zip – then change the extension to pbix and opened the file.

Opened the Get Data experience and switched to Azure Group and choose Azure Analysis services database

 

And entered the server and database information

And the report is now connected to an Azure Analysis Services 😀

This file can now be published to any workspace as it isn’t connected to the Power BI service – dataset.

This will of course give us a new dataset in the service as it is pointing to the Azure Analysis Services

You should also check out what you can do with the Power BI Rest API if you are interested in automating the creation of reports and changing connection to data set – but you can’t do the rebinding of a report to a power bi dataset using the API’s (but it would be nice if we could)

Link to documentation about the Power BI Rest API’s – find them here

Please Please

 

Please let me know if you find this useful by adding a comment or a like on this post.