Power Bi group by Index

It can often be useful to create an index column in power bi. But power Query does not allow you to create a grouped index for a specific product category. Let’s predict this simple example:

Image 004.png

As you can see we have two columns, one that contains the group (column 1) and one that contains values ​​(column 2). First of all on power query we have to add the index column starting from 1 and close the window.

Image 005.png

Now that we have the index column, the next step is to create a calculated column that uses the following measure:

SequenceColumn1 =
VAR CurrentValue = Sheet2[Index]
ALLEXCEPT ( Sheet2; Sheet2[COLUMN 1] )
Sheet2[Index] <= CurrentValue


  • Current Value: is the column used to order our value. In our case is the index but we can order the column using a specific value
  • Allexcept: have to contain the column used to do the group by of countrows
  • Sheet2[Index] <= CurrentValue: is the same value used to create order

The result is :

Image 006.png

As you can see now the index is grouped by a specific attribute and is repeated for each attribute combination.

Power Bi Map BOX

While using Twitter I found a new interesting map to use on power bi. It’s called Mapbox and is a service to be activated and used to generate maps in power bi destkop:

Image 003.png

To activate this type of map, you must first go to the https://www.mapbox.com website and register for the site.The service is paid, but under a certain limit of views is completely free. In particular:


Once registered, on your home page you have to select the “Access Tokens” tab and then click to create a new Token. The Token will serve us to enable the power bi map. Now let’s go to power bi desktop, click on the “From marketplace” tab. then in the window select map and go down until you find “Mapbox Visual Preview”:

Image 1

Then add our custom visual and select the longitude and latitude fields (remember to have correctly configured longitude and latitude on power bi). At this point the custom visual will tell us that it needs the access token. Let’s go back to the site where we generated the token, copy it and paste it into the “Token” text-box under Viz setting in the Format custom visual section:

Image 001


The game is done! now you can safely use the map. Why use this map instead of the others?

  • it’s very quick to create the map
  • has many usable standard views
  • it is easy to use
  • allows you to create completely custom maps with customizable icons based on projects and maps of others using a specific and user friendly tool on https://www.mapbox.com/mapbox-studio/

Image 002.png