R graphs and tables in Power BI Desktop


Power BI Desktop enable users to use R script visual for adding custom visualization generated with R language – regardless of R package used. Before using R script visual, you will need to enable it by setting path to R Engine on your client in the global options. Once this is done, you will be able to enhance your Power BI reports using R visualizations.


My personal favorite R packages I like to use in Power BI are shiny, plotly, ggplot2, googleVis, ggiraph, rCharts and d3js.  Apart from finding the package for data visualization where you feel home, one of the very great thing I find with Power BI + R is the ability to have visualizations created dynamically (more or less like if you would be using plotly or shiny)  with slicers and selectors. And this makes data exploration, data wrangling so much easier.

With October 2016 Power BI…

View original post 566 altre parole

Use Excel files on sharepoint with power bi

This post was create by Ken Pulse and Miguel Escobar and published on power bi Blog. I thinks it’s very interesting use of Power BI and Sahrepoint and i suggest you to read it:


Have you ever wanted to consolidate, combine or append data from multiple excel spreadsheets or workbooks? I bet you have, and it has been a painful process either done manually or via 3rdparty add-ins. In this blog post, we’ll show you how, with the newest version of the Power BI Desktop and Power Query for Excel, you can combine multiple data from Excel files into one big tall table.

The Scenario


In this specific case, we have an Office 365 group that we’ve created where we store some external sales data that do not come from our system. These files are provided by a 3rd party in order to give us a better understanding of the whole market and how well some products are doing on each market. Here’s a few remarks about our case:

Each of these files has only 1 month of data  we’ll have a file for January with just 1 sheet in it that will have all of the data for January.

These are all Excel files living a happy life in SharePoint – we get these files via email from the provider and then store them on our Office 365 group every month.

All of the files have the same structure, but different sheet names – all of the files only have 1 sheet and all of them have the same headers, but in a real case scenario the Power BI desktop and the process described here will work if you don’t have the same headers on all of the files.

Now, what we want to do is something that can be summarized in a series of steps like the following:

  1. Access all of the files from our site – we need to connect to our O365 group or SharePoint site and get the files first
  2. Combine all of the data from the files into one big table – once we connect to this data source, we need to be able to combine/consolidate/append all of the data from each of those excel files into one big table
  3. Clean and transform the data – ultimately, we want to unpivot some columns and also make the table more manageable to use with the Power BI Desktop and the DAX language.

In the end, we want the result of the combination to look similar to this:


Getting Data from SharePoint

First things first, we need to get the data and the Power BI Desktop makes it easier for us to get our data from either SharePoint or any O365 Group storage.

From the Get Data experience, let’s find the option for “SharePoint Folder”. This option will only require a text string which would be the URL for a SharePoint site.


Where can I find this so called url? Which one of the urls found in SharePoint should I use??

Good question! What the tool is expecting here is the root URL to the SharePoint site itself. Here’s a quick example:


My SharePoint site root url is https://powerbipanama.sharepoint.com/, but all of my files are actually in another site that starts with https://powerbipanama.sharepoint.com/sites/externalsales/ URL.

In order to use the correct URL, we need to be in the folder of the data that we’re trying to get and check the url that our browser shows. If it has the if it starts with the format of https://<site address>/sites/<sitename>/ then we need to use that url, otherwise we use the much simpler https://<site address>

In my own case, I’ll be using the https://powerbipanama.sharepoint.com/sites/externalsales url in order to connect to my site.

Once you input the url, you’ll then be asked for your credentials – be sure to select the “Organizational” type for your credentials and enter the same credentials that you use for your SharePoint site.


Now that we’re in, we’ll be able to see all of the files from all of the folders that we have access to in that specific site. This might be a pretty long list, so let’s do some filtering on the “Folder Path” column and select the folder where your data is being stored so you only get the files from it.


Filtering is key so you always get the files from the folder that you want. You can select multiple folders if you’d like as well.

We now have all of the files that we want to combine but, how do we combine these files?? What kind of magic is needed to consolidate all of them? Surely it can’t be just 1 click to combine them, right? Let’s find out.

New functionality! Combine Excel files with just 1 click


You might remember this popular posts from Microsoft itself on how to combine text and CSV files. It used to throw weird results with Excel files, but that changed with the latest version of the Power BI Desktop and Power Query.

This is now what happens after you click that button:

1. A new window will appear called ‘Combine Binaries’ – it will first find the first Binary from the table, which in our case is 03-March.xlsx, and it’ll create a sample function that will be applied to all of the other binaries.


We recommend always selecting the ‘Sample Binary Parameter’

In this window, you’ll see all of the objects found in the 03-March.xlsx file. If you select for example the 03-March spreadsheet, the same process will be applied to the rest of the binaries so Power Query/Power BI Desktop will extract the data from the 03-March spreadsheet from each file.

This is not our case. Each of our files has a different sheet name, so we recommend for these of cases to select the “Sample Binary Parameter” and click OK on this window.

2. Power Query / Power BI Desktop will do all the heavy lifting for you – you’ll end up with a table like the one we see in the next image where you can see all of the objects from each file. In our case, we see from the “Kind” column that all of our objects are simply sheets from the workbook and on the Name column we see the name of the workbook where each object comes from


After just 1 click, you have all of the data ready to be combined on the “Data” column. The elements within the red and orange outline were automatically created by the tool.

3. Expand the data found in the Data column – you’re one step away from consolidating all of your data! Just click the button with the double opposite arrow found on the “Data” columnclip_image022

Be sure to always click on “Load more” so you can expand the columns from all of your tables

At this point, we can check that our data has been consolidated in one table


YES!!!! All of the data from all of the 6 Excel files has been appended and now we have a table with all of this data.

Some more cleaning and transformation steps


After everything has been consolidated, we jump ahead and perform some cleaning and transformations shown within the outlined orange area

Here’s a quick list of the final steps that we did:

  • Removed Columns – we removed some unnecessary columns like the “Kind”, “Hidden” and “Item” column
  • Promoted Headers – after expanding the data, we noticed that the first row had the column headers so we immediately promoted the first row to become headers of the table
  • Renamed Columns – we simply renamed some of the columns like the “Name” column, which had the name of the product, to be named “Product Name”
  • Filtered Rows1 – since all of our files had headers, some of them were found in some rows, so we filter those out
  • Unpivot Other Columns – we used the unpivot feature on all the columns except the name of the month and the name of the product so we could get the “Attribute” and “Value” columns
  • Renamed Columns1 – we renamed the “Attribute” column to be “Market” and the “Value” column to be “Sales”
  • Sorted Rows – we sorted the data (ascending) by the Month column
  • Changed Type – we defined the data types for each of the columns and hit Close & Apply

The Result


Last but not least, get creative and create a report based on your consolidated data!

Of course, you could integrate this new table to an existing data model to add even more context to your sales performance and understand how you’re doing in each market.

Power BI Mobile December News

Microsoft some days ago introduce the latest update for Power BI mobile apps! Within this update, you’ll find these new capabilities:

  • New and improved Annotate and Share (iOS): improved the menu and user experience to make it even easier and quicker to annotate and share.can now also share an annotated report or mobile-optimized report directly from your Power BI app. when you share a snapshot via email we will also generate a deep link to the tile or report. When your colleague receives your email, they can continue their exploration in Power BI by clicking the link


  • Request access to dashboards directly from your mobile app (iOS) For those who scan a QR and don’t have access to the original dashboard, they can now submit a request for access directly from their mobile app.
  • Android Tablet preview
  • Geographic filtering (Android) ability to filter reports based on your current location on android app. (and with ios)
  • Apple watch improvements (iOS) : You can now refresh Apple Watch data directly from the Watch app
  • Custom URL on image tile (iOS, Android): If an image tile has a custom URL defined by the dashboard owner, when you tap the tile you go directly to that URL without opening the tile in focus mode
  • Offline background refresh adjustments (All): The refresh of data is performed in the background of the app, that ensures your business information will be up to date, even if you haven’t accessed it for a while.

Pyramid 3d on power bi

Microsoft released a new custom visuale create by Collatbion a 3d Pyramid.A pyramid chart represents data in the form of percentages, with the whole chart representing 100%.


Download Pyramid 3D chart by Collabion from the custom visuals gallery.

Pyramid 3D charts work best when you want to visualize data that diminishes in value with every successive stage.

You can utilize a 3D pyramid chart better by following the tips mentioned below:

  1. Sort the data in the chart.
  2. Click on one of the segments to make it pop out.
  3. Hover the pointer over one of the segments and the tooltip will give you a snapshot of the data without having to switch back to the data source.
  4. Customize chart title to indicate the subject matter more clearly.
  5. Try to avoid using lighter shades of colors in the chart.

LiveTiles on power bi

LiveTiles, an external company from microsoft, announce full integration with Power BI, allowing customers for the first time to visualize their data and other business content on a single pane of glass.

customers are being able to create meaningful dashboards that also can surface contextual documentation, reports, rich media and social feeds alongside highly visualized data sets.

Try LiveTiles for free for 14 days at www.livetiles.nyc

Data alerts in Power BI service

Set alerts to notify you when data in your dashboards changes beyond limits you set. Alerts work for numeric tiles featuring cards and gauges. Only you can see the alerts you set, even if you share your dashboard. Data alerts are fully synchronized across platforms.

How to do it?

  1. Start on a dashboard. From a dashboard tile, select the ellipses.
  2. Select the bell icon  to add one or more alerts for Total stores.

  3. To start, ensure the slider is set to On, and give your alert a title. Titles help you easily recognize your alerts.

  4. Scroll down and enter the alert details. In this example we’ll create an alert that notifies us once a day if the number of total stores goes above 100. Alerts will appear in our Notification center. And we’ll have Power BI send us an email as well.

  5. Select Save.

And if you want to change what you have done before?

From the tile itself

  1. If you need to change or remove an alert for a tile, re-open the Manage alerts window by selecting the bell icon . All the alerts that you’ve set for that tile are displayed.


  2. To modify an alert, select the arrow to the left of the alert name.


  3. To delete an alert, select the trashcan to the right of the alert name.

From the Power BI settings menu

  1. Select the gear icon from the Power BI menubar.


  2. Under Settings select Alerts.

  3. From here you can turn alerts on and off, open the Manage alerts window to make changes, or delete the alert.

Enabling Multiple Remote Desktop Connections in Windows Server

To enable multiple remote desktop connections in Windows Server 2012 or Windows Server 2016, you’ll need to access the server directly or through Remote Desktop. Once you’ve logged in, press the Windows key in Windows Server 2012 to open the Start screen or simply type the following into the Start bar in Windows Server 2016: gpedit.msc.

This will launch the Group Policy Editor (gpedit.msc), which is a management console through which you can configure many Windows system properties or run scripts.

Once the Group Policy Editor is running, navigate to:

Computer Configuration > Administrative Templates > Windows Components > Remote Desktop Services > Remote Desktop Session Host > Connections.

From here, first set the Restrict Remote Desktop Services user to a single Remote Desktop Services session parameter to Disabled.

Next, double-click on Limit number of connections and then set the RD Maximum Connections allowed to 999999.

Power BI November News

Today we released the November update of the Power BI Desktop. Let’s see the news:

Report View

  • Dropdown slicer:When you create a normal list slicer, you can turn it into a drop down by using the dropdown option next to the Clear button.
  • Hierarchical axis: improving the experience around viewing visuals that contain a hierarchy. You can use hierarchical labels by turning off the label concatenation experience in the formatting pane.


  • Matrix conditional formatting You can turn on conditional formatting by opening the field well menu for the measure and selecting Conditional formatting.
  • Table & matrix column formatting ability to format columns in tables and matrices. This lets you set the font and background color for any column.
  • Axis label and title color control you can now format your axis title color separately from the axis labels.
  • Mobile reports scrolling You can now extend the length of your mobile report and allow users to scroll to see the additional content.


  • Clustering (preview): Clustering lets you quickly find groups of similar data points in a subset of your data.You can use clustering on your scatter chart by clicking the “…” and then selecting Automatically find clusters.When you select this, a dialog opens where you can decide how many clusters you want us to find. If you leave it blank, we will automatically find the number of clusters we think makes the most sense with your data.


  • Forecasting – now available on Power BI on the web: With this release, the feature is generally available to everyone, so you no longer need to enable it through the Options dialog. Additionally, you can now see your forecasting in reports on the web and pinned tiles.
  • Groups – ribbon entry point You can create and edit groups from the Modeling tab. You can also create a new group from the Data / Drill tab when you are on a chart.

Data Connectors

  • Spark DirectQuery This includes the HDInsight Spark connector (under Get Data -> Azure) as well as the Spark connector for other Spark distributions (under Get Data -> Other).
  • OData connector – folding of expand record operations: adding support for the underlying OData Service doing the expand record operation rather than having this operation done in Power BI.
  • Enhanced “combine binaries” experience:
    • It analyzes the input files and detects the right file format to use
    • it provides you the chance to select a specific object from the first file
    • It automatically creates a few different artifacts
  • Azure Analysis Services: You can connect to Azure Analysis Services in the Power BI Desktop.To connect, select Analysis Services in the Get Data experience.
  • Web connector updates: new datasets that leverage import from Web pages won’t be automatically configured for refresh in Power BI on the web and will require the use of a gateway.

Query Editing Improvements

  • Improved “function authoring” experience: When a function definition has been created based on another query (i.e., using the “Convert to Function” option on the Queries context menu), a link will be created between the original/exemplar query and the newly generated function, so that you can simply modify the exemplar query steps and the linked function will be automatically updated for them.
  • Support for percentage data type: An input value such as “5%” will be automatically recognized as a Percentage value and converted to a 2-digit precision decimal number
  • Maximize/Restore buttons in the Navigator and Query Dependencies dialogs: The Navigator and Query Dependencies dialogs support window resizing by dragging the bottom/right edges of the dialog.In this release it’s possible to maximize/restore these dialogs by exposing Maximize and Restore icons in the top-right corner of the dialogs so that they behave like standard Windows dialogs.

Deleting all duplicate rows but keeping one

When you work with Power Bi or Power Pivot duplicates are not allowed but due to different problems or error I know there are some duplicates in this table.

I need to eliminate the extra rows from the perspective of the key columns. Some other columns may have slightly different data but I do not care about that. I still need to keep one of these rows however.

SELECT DISTINCT won’t work because it operates on all columns and I need to suppress duplicates based on the key columns.

How can I delete the extra rows but still keep one efficiently?

It’s very simple you have only to use this query:

WITH cte AS (
  SELECT[KEY1], [KEY2], 
     row_number() OVER(PARTITION BY KEY2, KEY1 ORDER BY KEY1) AS [rn]
DELETE cte WHERE [rn] > 1

With this simple query you can delete al duplicate row keeping one. You can use order by to keeping for example the last raw (order by date asc).

Create a dynamic (automatically updated) Excel dropdown

If you often edit the items in the drop-down menu, you may want to create a dynamic drop down list in Excel. In this case, your list will get updated automatically in all the cells that contain it, once you remove or add new entries to the source list.

The easiest way to create such a dynamically updated drop-down list in Excel is by creating a named list based on a table. If for some reason you prefer a usual named range, then reference it using the OFFSET formula, as explained below.

  1. You start by creating a usual dropdown based on a named range as described above.
  2. In step 2, when creating a name, you put the following formula in the Refers to box.=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)


    • Sheet1 – the sheet’s name
    • A – the column where the items of your drop-down list are located
    • $A$1 – the cell containing the first item of the list

As you see, the formula is comprised of 2 Excel functions – OFFSET and COUNTA. The COUNTA function counts all non-blanks in the specified column. OFFSET takes that number and returns a reference to a range that includes only non-empty cells, starting from the first cell you specify in the formula.
Creating a dynamic drop-down list in Excel using the OFFSET formula


The main advantage of dynamic drop-down lists is that you won’t have to change the reference to the named range every time after editing the source list. You simply delete or type new entries in the source list and all of the cells containing this Data Validation list will get updated automatically!