Tag Archives: Power

Power Query (M)agic: Dynamically remove leading rows AND columns



Does Power Query sometimes seem too rigid? It’s great for defining a sequence of transformations to clean your data but requires the incoming data to be in a consistent format. Today, I want to talk about a technique for dynamically handling sheets with different structures.

You can download a sample .pbix to follow along with me here.

Let’s look at my sample data. Suppose my sales manager sends me a monthly sales report structured similarly to that below:

Nar Table 1 1024x199 Power Query (M)agic: Dynamically remove leading rows AND columns

This table looks pretty clean, right? Using Power Query, you can load this sheet, remove the first two rows and columns, unpivot the data, and you have the numbers you’re looking for in tabular format.

Patting yourself on the back, you file the report away until February rolls around. The Sales Manager sends you the new numbers, and you hit refresh, and… an error! Peaking at the data, you see that he changed the layout of the file and also added new salespeople to the matrix!

Nar Table 2 1024x333 Power Query (M)agic: Dynamically remove leading rows AND columns

Nar Table 3 1024x334 Power Query (M)agic: Dynamically remove leading rows AND columns

As enjoyable as it would be just to tell the manager to stick to a consistent format, sometimes this isn’t realistic or feasible. So, let’s look at how we can use Power Query to handle this chaos:

  1.  Identify the header name for the column that demarcates the beginning of the matrix.
  2.  Automatically detect rows up to that header row.
  3.  Automatically detect columns up to that header column.
  4.  Remove the extra rows and columns, leaving you with a squeaky-clean dataset.
  5.  Unpivot the matrix for each sheet and append into a single dataset.

STEP 1: IDENTIFY THE TARGET HEADER ROW AND COLUMN:

Looking back at our sample data, you can see that there is a common cell in all our sheets that lies at the upper left of the data we want to extract. In this case, the target cell is the “Region” header. We want to remove all rows ABOVE that cell and all columns LEFT of that cell without hard-coding the location of that cell.

STEP 2: LOCATE THE HEADER ROW DYNAMICALLY:

We need some way to identify where the header row starts so that we can remove rows up to that point. This functionality is something that I would have thought is built in by default, but surprisingly is not! Luckily our friend Ken Puls over at ExcelGuru.ca came up with [a solution] for this which I’ve adapted slightly for our purposes.

Load your first worksheet into Power Query, add an Index column, and filter the table to the target value from step 1:

  1. Add Column > Index column > From 0.
  2. Add a custom step (click the fx button in the formula bar).
  3. Replace the formula with some custom M code: Table.FindText(#”Starting Table w/Index”, “Region”). Name the step “Filtered to Header Row.”

Nar Table 4 1024x322 Power Query (M)agic: Dynamically remove leading rows AND columns

Note that you’ll want to replace the yellow-highlighted text with the Target Header name from step 1. Table.FindText() scans the entire table for the target text and returns any row with that value. So be careful that your dataset doesn’t have that exact target word in other places!

Now we have our header row isolated along with the Index value for that row. Rename the step to “Filtered Header Row” as we’ll come back to this shortly.

Nar Table 5 1024x118 Power Query (M)agic: Dynamically remove leading rows AND columns

STEP 3: DETECT THE COLUMNS TO DELETE

Let’s move on to the more difficult part: dynamically removing leading columns. We have a bunch of columns, and we want to eliminate the first X, up to our target column. We’ll leverage the technique above and add some P3 secret sauce.

First, transpose the #”Filtered Header Row” step and add an index. That will make a single row table into a single column table that we use to identify the columns to remove.

  1. Transform > Transpose.
  2. Add an index column: Add Column > Index column > From 1.
  3. To handle blank columns in the header row (always a possibility in dirty data), add a custom column that checks if the column list has any nulls: Add Column > Custom Column > if [Column1] = null then “Column” & Number.ToText([Index]) else [Column1].

Our goal is to delete all columns left of the “Region” column (or above “Region” in the transposed table) so let’s find the index of that row:

  1. Right Click Column1 > Text Filters > Equals > “Region”.

Nar Table 7 2 Power Query (M)agic: Dynamically remove leading rows AND columns Equals > “Region”” width=”568″ height=”79″ srcset=”https://powerpivotpro.com/wp-content/uploads/2018/02/Nar-Table-7-2.png 568w, https://powerpivotpro.com/wp-content/uploads/2018/02/Nar-Table-7-2-300×42.png 300w” sizes=”(max-width: 568px) 100vw, 568px”>

We’re building upon Ken’s technique of finding the Index that corresponds to a target cell but this time with a transposed table. Since we’ll reference this number a couple of times, let’s Drill Down to just the Index number so that we have an easily referenceable step:

  1. Right-Click on the Index value > Drill Down.
  2. Rename that step to “TargetColumnIndex”.

Now, jump around a bit and reference the original column list and filter it down to include ONLY the rows that have an index number less than the target column.

  1. Click the fx button to insert a New Step.
  2. Revise the M code to point to the full column list: = Table.SelectRows(#”Added ColumnName”, each [Index] < #”TargetColumnIndex”.

Let’s break down what we’re doing here: the outer Table.SelectRows filters the inner table #” Added ColumnName” down to all rows that have an Index less than the “TargetColumnIndex” value we isolated a couple of steps ago.

Nar Table 8 1 1024x612 Power Query (M)agic: Dynamically remove leading rows AND columns

Finally, remove the helper columns keeping only “ColumnName,” and you have a nice list of columns to exclude!

STEP 4: REMOVE THE ORIGINAL EXTRA COLUMNS AND ROWS

We now have all the pieces we need to eliminate our junk rows and columns! Let’s jump back to our original query and clean it up.

Create a new step and change its code to reference the Starting Table:

  1. Click fx > rename step to “Back to Starting Table” > change code to = #”Starting Table”.
  2. Home > “Remove Top Rows.” Enter any value for the parameter.
  3. Edit the M code directly and change the 2nd parameter of Table.Skip(#”Back to Starting Table,” #”Filtered to Header Row”[Index]{0}), in this case, we want to reference the step where we isolated the header row number from earlier.
  4. Home > Use First Rows as Headers.

Nar Table 9 2 1024x409 Power Query (M)agic: Dynamically remove leading rows AND columns

Boom! We’ve dynamically eliminated the top rows!

Now for the final act, we’ll tweak Table.RemoveColumns (when you do “Remove Columns” Power Query uses this function) to use a dynamic parameter! Remember that list of columns we generated earlier, the list we want to extract? That’s what we’ll input into Table.RemoveColumns.

First, select any one of the junk columns and right-click > “Remove Columns.” Take a look at the formula that Power Query generated.

  1.  Table.RemoveColumns(#”Promoted Headers”,{“Column1”}).

We know that Table.RemoveColumns requires a list for its second argument, so we can reference the list of columns to remove from an earlier step:

  1. Table.ToList(#”Columns to Remove”).

Now we’re left with a clean matrix which we can easily unpivot without any problems.

    1. Right-click the Region column > Unpivot Other Columns.
    2. Rename columns something useful.

Nar Table 11 848x1024 Power Query (M)agic: Dynamically remove leading rows AND columns

STEP 5: Convert magic query to function

  1. The final step is to convert your magic query into a function so that you can apply this dynamic transformation to each file/worksheet that needs to be unpivoted.

Conclusion:

Using the technique of identifying a target row and column, you can create extremely powerful and dynamic queries that can handle input files that aren’t always perfect, because let’s face it, you can’t always rely on training or data validation to prevent end users from modifying your perfect templates. Our job as data modelers is to make the end user experience as friendly as possible by foreseeing and handling exceptions.

If you have any ideas on how you might use this in your reports, please feel free to share in the comments section!

We get it:  you probably arrived here via Google, and now that you’ve got what you needed, you’re leaving. And we’re TOTALLY cool with that – we love what we do more than enough to keep writing free content.  And besides, what’s good for the community (and the soul) is good for the brand.

But before you leave, we DO want you to know that instead of struggling your way through a project on your own, you can hire us to accelerate you into the future. Like, 88 mph in a flux-capacitor-equipped DeLorean – THAT kind of acceleration. C’mon McFly. Where you’re going, you won’t need roads.

Let’s block ads! (Why?)

PowerPivotPro

Dynamically switching axis on visuals with Power BI

An interesting visualization pattern I have seen is that some customers want to be able to switch the axis on the chart dynamically using a slicer.

Let’s take a simple model like this:

 Dynamically switching axis on visuals with Power BI

where I want to be able to dynamically change the axis of my chart to be Currency, Country or Region using a slicer. Today that is not possible out of the box because you cannot have a single slicer crossing different table. Now one option here is to use bookmarks to switch the chart based on a label (but that is cheating  Dynamically switching axis on visuals with Power BI)  but I rather fix it in the model. What we need to do is bring all slicer values and their keys into a single table to be used in the slicer, for this use a DAX calculated table like this:

Table =
var currencyt = CROSSJOIN(ROW("Type","Currency"), VALUES(DimCurrency[CurrencyName]))
var country = CROSSJOIN(ROW("Type","Country"), VALUES(DimSalesTerritory[SalesTerritoryCountry]))
var region = CROSSJOIN(ROW("Type","Region"), VALUES(DimSalesTerritory[SalesTerritoryRegion]))
return UNION(UNION(currencyt,country), region)

First we use 3 variables to create 3 tables that each joins with the name we want on the slicer with the key values of that dimension. Finally we join all of them into a single table, i also rename the CurrencyName column to Values.

This gives us a single table:

 Dynamically switching axis on visuals with Power BI

Now we can create a slicer based on the type and create a chart based on the Key column

 Dynamically switching axis on visuals with Power BI

Now finally I write a measure that joins the names of the axis with the values on the dimension using TREATAS.

Measure 2 = 
if(HASONEVALUE('Table'[Type]),
          SWITCH(VALUES('Table'[Type])
                 ,"Country", CALCULATE(SUM(FactInternetSales[SalesAmount])
                                      ,TREATAS(VALUES('Table'[Values])
                                      ,DimSalesTerritory[SalesTerritoryCountry]))
                 ,"Currency",CALCULATE(SUM(FactInternetSales[SalesAmount])
                                       ,TREATAS(VALUES('Table'[Values])
                                       ,DimCurrency[CurrencyName]))
                 ,"Region",CALCULATE(SUM(FactInternetSales[SalesAmount])
                                      ,TREATAS(VALUES('Table'[Values])
                                      ,DimSalesTerritory[SalesTerritoryRegion]))
)
)

This calculation pushes the filtered down onto the dimension based on the slicer, it is using the filtered values from the  values column into each column of the dimension. This is as if the filter was placed on the dimension instead of the table we just created.

This finally gives us the ability to slice on a dimension:

 Dynamically switching axis on visuals with Power BI

 Dynamically switching axis on visuals with Power BI

You can download the PBIX file here.

Let’s block ads! (Why?)

Kasper On BI

Power BI used for Surfing FUN!


Surfs Up

A long time ago a peer in Financial Planning and Analysis said “Financial Planning starts in the home.”  I modified the statement to “Data analytics starts in the home.”  This means that I have a host of personal databases and analytics dashboards.

When I was a kid, I counted fish by the thousands and wrote them all down in a loose-leaf binder.  I grew up discovered Excel and kicked myself for missing out on this beautiful tool when I was ten years old. Many moons later I learned Microsoft Access and would eventually count calories by the millions learning less is more.  I bought an electricity monitor and started measuring the wattage used in my house every minute for more than five years, tens of millions of watts.  My conclusions were the light bulb was killing my electric bill.  Those old, smoky incandescent bulbs were to blame  CFL’s, and LED’s cut my bill in half overnight. These skills extend to my work life, counting loaves of bread in the hundreds of millions, barrels of oil in the billions and most recently vitamins in the hundreds of billions. Some people say I have an obsession with counting.

I live on the coast of California in Half Moon Bay.  I surf to stay sane in an otherwise crazy world. A decade ago, I converted to surfing Stand Up Paddling boarding to catch more and bigger waves. I became an accomplished big wave surfer and most recently a foil surfer. I found a great way to combine my passion for counting with surfing and on October 18, 2012, I started counting waves.  Like counting fish, I would mentally tally up the waves, come home and write them down this time in my favorite medium Microsoft Excel7,379 waves later I bought a TRACE GPS monitor for surfing.

You can follow along by downloading the TRACE gpx file here and the .pbix workbook here.

The TRACE attaches to a surfboard and records the GPS coordinates along with speed and direction.  After a surfing session, I connect my cell phone and TRACE will upload the data and return a nice pretty picture of every wave that I caught overlaid, with Google Maps I assume, on the exact spot I just surfed.

A typical TRACE view looks like this:

clip image002 thumb Power BI used for Surfing FUN!

I wrote the designers of Trace, David Loskin, and asked if I could get access to the data directly. He sent me a developers handbook and started to download the GPX files to a box site. Two years ago I attempted to uncork the data using Excel. The GPX format was spicy enough that I couldn’t cleanly open up the row-based data. I gave up for more than a year.

In September of 2017, I started to study the effects of fins on surfboard performance. Specifically, I was interested in how fast was I going with the various fins I was using on my surfboard. Was a single fin, thruster or quad setup faster? I took another attempt at uncorking the TRACE data.

I work for a firm with heavyweight data scientists. I grabbed one of the millennials named Ben and said “Ben can you crack open these GPX files? I need to get the detailed speed and trajectory data from these files.” Ben hammered the files and said, “Wow, this data has longitude, latitude, azimuth, speed, and elevation, no way!”

I figured I would make another attempt to crack open the GPX files. I had recently taken a course with PowerPivot pro on DAX and PowerQuery.Where I had failed with Excel to open the GPX files, PowerBI and PowerQuery would shine.

In summary, I do the following:

  1.  Download the latest GPX file to a common directory
  2. Define using a set of Parameters the latest “GPX Source” for the PowerQuery retrieve
  3. Extract the MetaData from the GPX File
  4. Use the Parameters to Perform a Full Data Pull of the GPX files
  5. Create a Secondary pull of the GPX file à Offset the data by one record
  6. Define a Wave-based upon changes in the row base data recorded in the GPX file
  7. After I surf and log all my GPX (GPS) data from TRACE, I will upload my data using the application on my iPhone.

1. Within a few minutes, the GPX file shows up in my sync’d box site. I then download the GPX file to a directory on my PC.

The files look like this:

clip image004 thumb Power BI used for Surfing FUN!

2. I built a Parameter table to define the “GPX Source” for each pull from the GPX file. The Parameter table may be beyond the scope of this write-up, but it essentially allows me to use an applet for each time I update the PowerBI visuals to define a specific pull from the GPX file. The parameter definition is as follows allowing me to specify the A) FilePath, B) File and C) extension. A and C rarely change, so I just update B and hit refresh from the PowerBI interface.

clip image006 thumb Power BI used for Surfing FUN!

3. The GPX MetaData then pulls specific attributes about the file using the “GPX Source” defined above. In this example, the “Source” is defined as = #”GPX Source” which is referencing the preceding query step. Some file attributes can be accessed by double-clicking on the various field “TABLE” references as shown in Step 1 below.

Step 3 Part 1

clip image008 thumb Power BI used for Surfing FUN!

Step 3 Part 2

Expanded the “Table” of available information in the Metadata field.

clip image010 thumb Power BI used for Surfing FUN!

Step 3 The final step

Delivering a concatenation of my username with the GPS identified location, provided by TRACE and the Date of the session

clip image012 thumb Power BI used for Surfing FUN!

4. To pull the full GPX data, I once again reference the “GPX Source” from step #2.

clip image014 thumb Power BI used for Surfing FUN!

The great thing about PowerBI is that you can simply keep clicking around on a foreign dataset and eventually learn how to crack it open. In this case, a few clicks through the data file and I discover the following fields of data in the GPX file:

· Time: Every 1/5th of a second

· Speed: Meters per second

· Azimuth: Orientation North / South

· Latitude

· Longitude

· Elevation Meters

· Sub Seconds Measured in 1/100 of second increments

clip image016 thumb Power BI used for Surfing FUN!

5. The GPX data delivers a static record per 1/5th of a second. During a 1+ hour session, this results in 10,000’s records of data. To identify a wave caught requires measuring an increase in speed from one record to the next. In this example, I am creating a second pull of the GPX file and offsetting it by one record. By merging the file with itself, I allowed for a comparison of one record with the next. A significant jump in speed, for example, from below, to above six mph, can be used to identify the start of a wave.

6. Once a wave was identified, from a discreet change in speed above the threshold six mph, a summary of all measured waves was built in an additional table grouping the attributes, based upon start time, of each wave.

clip image018 thumb Power BI used for Surfing FUN!

The Final result allows for a linkage between the “Waves” table and the Standard GPX pull linking on the WaveStart field further filtering on the True Wave field = “Yes.”

Selecting Home à Edit Queries à Edit Parameters bring up the following applet:

clip image020 thumb Power BI used for Surfing FUN!

Selecting OK and apply performs a full retrieve of the source data using PowerQuery to load the visualization.

The result is delivered to Power BI’s front end allowing the user to select each True Wave defined above as lasting more than 4 seconds and longer than 50 yards. The Latitude and Longitude are fed to a custom visual “Route Map” imported from the Power BI store. The Route Map overlays each ride on a picture of the local surfing spot with the orange line. An additional visual at the bottom of the page shows a bar graph with the Minimum and Maximum speed for each second. The red line shows the change in elevation during the ride.

The picture looks like this and becomes completely interactive:

clip image022 thumb Power BI used for Surfing FUN!

This perspective was for a smaller day 4 to 7 feet on the inside of Mavericks in Half Moon Bay, CA. Even more thrilling was that I had been using my TRACE for more than two years. I had all the historical data and was suddenly awash with data.

Big Wave Drop

I went back and opened up data on this wave from Nov 12th, 2016. A big wave Saturday at Mavericks. Waves caught at Mavericks have shown to have a stepped nature. They drop, come up a little bit, and then drop again.

This data suggests I descended 17 feet on the initial drop, shown in the picture below. I then ascended 4 feet and then fell another 7 feet before kicking off the wave to the south.

A full drop from Peak to Crest would likely result in getting hammered by the wave. Best practice might suggest leaving a healthy trough below your feet and progressing down the line to survive until the next wave.

clip image024 thumb Power BI used for Surfing FUN!

clip image026 thumb Power BI used for Surfing FUN!

Big Wave Crash

Here was another wave caught on camera, video and with my TRACE.

The takeoff is shown in the 1st photo.

clip image028 thumb Power BI used for Surfing FUN!

I maxed out at 34 mph and was cruising in front of the breaking wave when a someone caught this image, a still frame, from the video below.

clip image030 thumb Power BI used for Surfing FUN!

The wave was fun for me but ironically made the World Surf League’s Wipeout of the Year entry for another surfer who was caught a bit deeper in the same wave.

WSL Wipeout of the Year

https://www.youtube.com/watch?v=CiEJpv0ALjk

I use my Power BI application with TRACE nearly every weekend to get an accurate wave count; distance traveled, maximum and average speed for the entire session. A video demonstration of the features of my TRACE applications is as follows:

https://www.youtube.com/watch?v=WZTZyLGFArE

Some more fun using the TRACE overlay on video I made of more modern Foil Surfing. TRACE provided the overlay, but you will see the Power BI graphic summarizing the flight at the end of the wave.

https://vimeo.com/243950048

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.

Let’s block ads! (Why?)

PowerPivotPro

Power BI Desktop February Feature Summary

There are two themes for this month’s Power BI Desktop update. The first is a focus on some of your major asks including multi-selecting data points across multiple charts and syncing slicer values across multiple pages of your report. We also have a major update to our SAP HANA connector. Our second theme is a focus on performance improvements across three different areas of our product.

5d7f8edd 60a2 45e6 8cd8 5a7fa35e97b5 Power BI Desktop February Feature Summary

Don’t forget you can now get Power BI Desktop from the Windows Store if you’re on Windows 10!

c36916c4 7a57 41f0 9e2f 5ba0ce3db9ed Power BI Desktop February Feature Summary

Here’s the complete list of February updates:

Reporting

Analytics

Custom visuals

Data connectivity

Other

For a summary of the major updates, you can watch the following video:

We are very excited to announce the release of one of our most highly asked for features! You can now ctrl+click on data points in multiple charts and have the cross filters applied to your entire report. As you select data points across multiple charts, we will apply both those filters together. For example, if you select Computers in a column chart and Regular in a pie chart, then all the charts on your report page will be filtered to products that are computers and regular.

1f2cc058 0fce 45d0 a098 dbf643eab96e Power BI Desktop February Feature Summary

Watch the following video to learn more about multi-selecting data points:

Another huge ask from the community is to have slicers that apply to multiple pages. To meet this request, we’ve added a “Sync slicers” pane that will let you select slicers to stay in sync across multiple pages. For instance, selecting “Red” on a product color slicer on page 1 will be reflected in the product color slicer on page 2 if the two pages are synced. You can reach the Sync slicers pane from the View tab.

Once you’ve opened the pane, select a slicer to see the options. You can quickly make the slicer apply to every page by clicking the ‘Add to all’ link. A hidden slicer will be put on every page, synced to the currently selected slicer. You can also use the right column to pick if the slicer is visible on each page or not.

3f76256d 598a 45c0 9349 cc13bbf4d7af Power BI Desktop February Feature Summary

Watch the following video to learn more about syncing slicers:

We also have a minor improvement to our numeric slicer preview visual. Now if you create a range slicer using a whole number column, it will snap to rounded values and not apply decimal filters.

8be77bce e748 46d1 85e1 7592ba1fbbb5 Power BI Desktop February Feature Summary

Watch the following video to learn more about syncing slicers:

Starting this month, we are planning on flighting a new, faster way to geocode your map data. This new method can have up to a 6x improvement on your geocoding speed depending on the amount of data you have. While you shouldn’t notice any changes to the actual location results you are getting back, the results will come back much faster. We wanted to be very safe with this change, so we are choosing to flight this feature. This means we will be releasing it to a small group of users and will over time slowly increase the number of users getting the feature. When we flight features, they only affect the Power BI service, not your Desktop reports, but we wanted to share the feature details here so everyone is aware of the changes.

By default, Power BI will hide your data labels if you place them inside the data points and they don’t fit. This month we added a formatting option that lets the data label overflow past the edge of the data point, like the experience you’d see in Excel. Note that we will still automatically hide data labels that would collide with others to prevent overlapping text.

f44dc7ec fc8a 4248 9aad 877e17e821ff Power BI Desktop February Feature Summary

Watch the following video to learn more about the overflow formatting option:

 

We have so many options now in our formatting and analytics pane that it can sometimes be hard to find the option you are looking for. To help with this, we’ve added a search box that will filter down the options to only those that match your search terms.

1205cb1c a526 49cd 88cb 332b2eedd935 Power BI Desktop February Feature Summary

Watch the following video to learn more about searching the formatting pane:

Until now, you could only bring your own date tables by importing from Excel (using PowerPivot) or through LiveConnect (SSDT). Now in Power BI, you can mark a table as a date table. This will enable date hierarchies and time intelligence for the selected table.

In order for a table to be marked as a date table, it must contain at least one column of type ‘Date’, or ‘Date/Time’.

You can mark your own table as a date table either through right-clicking on the desired table or using the ribbon button.

22180020 9248 4c8b bd6b 36dfcb862353 Power BI Desktop February Feature Summary

When marking a table as a date table, you must select a date column to be used as a primary date column. Make sure that the column you pick for this:

  • Contains no null values
  • Contains unique values
  • Contains contiguous date values (from start to end)
  • If it is a date/time column, the timestamp must be the same across each value

markAsDateTable Power BI Desktop February Feature Summary

When a table is marked as a date table, all our built-in date tables associated with this table will be removed. If you later un-marked it as a date table, our built-in date tables will be re-created for the date columns in the table.

Watch the following video to learn more about marking a custom date table:

Our quick measures feature is now generally available and no longer requires the preview feature switch to be turned on. Additionally, thanks to the above feature to mark your own custom data table, you no longer need to use Power BI’s date hierarchy for the time intelligence quick measures. You can use the date field from your own date table in any quick measures. 

We’ve added two new DAX functions this month that help if you’re working with date-time data across timezones. DAX has long supported the NOW() and TODAY() functions that return time and date in the timezone that the function’s being used – so if a .pbix file is passed to someone in a different timezone they’ll see different results. UTCNOW() and UTCTODAY() will always return the current time or date in UTC so you can guarantee consistent results wherever you are (and also when you upload the workbook to the Power BI service).

Custom visuals enable developers and users to visualize their data in the way that best helps them understand the data and gain insights from it. Often, these visuals are specific to the organization’s needs and the way that it runs its business. The organization typically doesn’t want to publish the visual to the public. Nevertheless, there is a real need to get those visuals into the hands of data analysts inside the organization, the ones that build and maintain the analytics reports and dashboards. It’s especially important that it’s easy for them to discover the appropriate visuals and to know they don’t need to maintain and update these visuals to use them.

Your organization’s Power BI admin can deploy specific visuals as part of your organizational repository. This means you’ll see these deployed visuals under a new section of the Power BI custom visual store integrated into Power BI Desktop called My organization. Since these visuals have been vetted by your company, you can have confidence that they are well made.

d236c9a2 cf4a 4c22 b991 1a5fbb0ce0df Power BI Desktop February Feature Summary

This feature is currently in preview, so you’ll need to enable it from the Options menu (File->Options and settings->Options->Preview features).

Learn more about organizational custom visuals.

If you are a Power BI admin, you can add new custom visuals to this part of the store by going to the Admin portal in the Power BI service and selecting Organization visuals tab. From there you can click the Add a custom visual button and upload the .pbiviz file for the custom visual you want.

ebf9eaf0 b9e7 40bd 8d4b 9caa2f768154 Power BI Desktop February Feature Summary

Learn more about deploying and managing organization custom visuals.

Watch the following video to learn more about organizational custom visuals:

The Network Chart by ZoomCharts has the ability to visualize data as a network structure and filter data through a variety of gestures and smart touches on all devices. The network structure lets you have multiple vertice types (more than two), making it possible to see the connections among many categories. The box select feature lets you easily select multiple nodes within the chart to filter your other visuals. Visualizing the data as a network structure lets you see the implied connections in data making important decision making more efficient and obvious.

5f4ee9d7 366c 4ad9 8782 a4728b9b9f87 Power BI Desktop February Feature Summary

Some practical use cases of the interactive Network Chart and Filter visual are:

  • Customer segmentation
  • Cost and revenue analysis
  • Log/Event analysis

There are two versions of this chart. The free version is available on Microsoft AppSource and the customizable paid version can be purchased on the ZoomCharts website.

Features of the free Network Chart visual include:

  • Fully depict data as a network structure with the ability to filter the data from within the visual
  • Smooth connecting visuals
  • Cross-category filtering
  • Dynamic force feedback layout
  • Expanding/collapsing vertices
  • Hide/Unhide vertices
  • Select one or multiple vertices
  • Category-coded colors for vertices (non-configurable)
  • Inner and outer labels for vertices (non-configurable)
  • Dynamic radius for vertices (non-configurable)

Paid features include:

  • Legend control
  • Customizable vertices
  • Support for images inside vertices
  • Value-based gradient colors for vertices and edges
  • Inside/outside label configuration
  • Configurable dynamic radius for vertices
  • Configurable dynamic width for edges
  • Edge decorations
  • Up-to 9 categories

Watch the following video to learn more about the Network Chart:

Slope Chart by MAQ Software allows you to analyze trends in data at a glance. This visual is useful for comparing interactions between two data points based on time or other user-chosen parameters. A slope chart is most useful if there are notable changes to highlight or for showing comparisons between groups.

Some key features of the visual include:

  • Quick comparison of indicator growth or loss across categories
  • Easy interaction with many data points using zoom functionality
  • Quickly download an image of the chart with the capture image widget

794c88a4 3075 45cd 9241 ddf7d45dfcf3 Power BI Desktop February Feature Summary

Watch the following video to learn more about the Slope Chart:

The Filter by List visual lets you bulk apply filter values for a report. Currently if you want to filter a report with a large number of categories, such as 100 products, you’d need to check each box in the filter pane one by one. With this visual you can do this immediately by pasting the entire list of products into the slicer visual.

201cd90b 2b68 4328 8aac d34215c6223f Power BI Desktop February Feature Summary

Watch the following video to learn more about the Filter by List visual:

This visual helps you find overlaps and gaps of dates in your data. This is useful for showing things such as team vacation schedules or project plans.

1e58f689 297f 44b6 bb3f 90280f05c8cf Power BI Desktop February Feature Summary

Watch the following video to learn more about the as Timeline visual:

T-accounts are used by most accounting professionals to understand difficult accounting journal entries and with this visual you can now create T-accounts in Power BI directly.

d6aaf731 1bee 499f 90af 395b9ee7e49a Power BI Desktop February Feature Summary

Watch the following video to learn more about the as T-Accounts visual:

Power BI has long supported SAP HANA, both for import and DirectQuery. However, the DirectQuery support largely treated SAP HANA as a relational source, meaning that care had to be taken to ensure that measures aggregated correctly when dealing with non-additive SAP HANA measures like Counters or ratios and to ensure that the reports had good performance.

There is now a preview of a new approach to SAP HANA that will treat it as a multi-dimensional source by default, in a similar manner to SAP Business Warehouse or Analysis Services. You’ll need to turn on the “New SAP HANA connector” preview feature switch under File > Options and Settings > Options > Preview features.

When connecting to SAP HANA using this approach, a single analytic or calculation view is selected, and all the measures, hierarchies, and attributes of that view will be available in the field list. As visuals are created, the correct aggregate data will always be retrieved from SAP HANA.

It is still possible to use the old approach to DirectQuery over SAP HANA. This is controlled by selecting File > Options and settings and then Options > DirectQuery, then selecting the option Treat SAP HANA as a relational source. Check this option to create a DirectQuery report over SAP HANA using the old approach.

Note that this option controls the approach that will be used for any new connections created. It has no effect on any existing SAP HANA connections in the current report, nor on connections in any other reports that are opened. This means that any reports connecting to SAP HANA that were created prior to February 2018 will continue to treat SAP HANA as a relational source.

Given the completely different behavior between the two approaches, it’s not possible to switch an existing report from one approach to the other.

The two approaches are described in detail in our documentation.

While in preview, reports created using the new multi-dimensional approach can’t be published to the Power BI service. Doing so will result in errors when opened within the service.

This latest update includes a large number of improvements to the performance of reports using DirectQuery. We expect some improvement to many queries and substantial improvement in particular cases. The key performance improvements are:

  • Combining additive and non-additive aggregates into single query. Power BI will now combine both additive aggregates (e.g., SUM, MIN) and non-additive aggregates (e.g., DISTINCTCOUNT) into a single SQL query to the underlying source, rather than sending two queries. This will improve the performance of any visual containing both DistinctCount and another additive aggregate.
  • Improved handling of queries with a large number of literals. The performance of any query involving a large number of literals has been improved, such as queries that involve measure filtering, TopN filtering, or bi-directional relationships.
  • Use of a single SQL query rather than multiple for some visuals and calculations. Power BI used to send several SQL queries using DISTINCTCOUNT for certain kinds of visuals and calculations. For example, this could occur when using bi-directional relationships, or using a quick measure, or in a measure using CALCULATE of DISTINCTCOUNT. Now, a single SQL query is sent, resulting in an order of magnitude improvement in some cases.
  • Optimized queries to reduce post-aggregation. In some scenarios, Power BI used to send SQL queries at a lower granularity than needed for the visual and perform post-aggregation locally. Now, it pushes all the joins and post-aggregations down to the underlying source, improving performance. For example, this applies to any visual where the underlying measure column is directly related to only a subset of the columns being grouped by (and there is some non-trivial filtering), and most quick measures.
  • Optimized use of TREATAS in calculations. Power BI will optimize common patterns for the use of TREATAS in calculations to combine into a single query. For example, a visual containing the following two measures:

    CALCULATE(SUM(Table1[Col1], TREATAS(VALUES(Table2[Col3]), Table1[Col3])) and
    CALCULATE(MIN(Table1[Col2], TREATAS(VALUES(Table2[Col3]), Table1[Col3]))

    would previously have resulted in two separate SQL queries, but will now result in just one.

  • Fewer SQL queries are sent when there is multi-column tuple filtering. For example, if Include or Exclude is used to filter out data items based on multiple columns, e.g., to exclude the items (“USA”,”Old”) and (“France”,”Young”) from a visual.

We’ve made improvements to our performance when opening and saving files, especially when your files are quite large. In some cases, you can see between a 40% and 60% performance improvement.

We’ve done some work to improve the Show items with no data feature.

1df339d1 7bc8 438a b2c0 e576b4cae245 Power BI Desktop February Feature Summary

Some visible improvements you’ll see are:

  • For visuals that have Show items with no data enabled, you’ll see major performance improvements for visuals with columns or filters directly related in the model (as in they have one to many relationship). On average we improved the performance by 100%.
  • Filters related to columns that have Show items with no data enabled now respect model bidirectional cross filtering

We are planning to release a new feature for reports in the Power BI service very soon where we will persist filters, slicers, and other data view changes as your report consumers last left them. This will be unique to each of them, so they will no longer need to spend their valuable time setting up or filtering the reports on every visit. Instead, they can pick up right where they left off!

In order to give you more control over this feature when it does get released in the service, we have added a new option under Report settings called “Persistent Filters” in this month’s release. The feature is on by default, but you can choose to disable it here.

61a4ec49 7854 4bc2 9d14 642529b665b6 Power BI Desktop February Feature Summary

Watch the following video to learn more about the persistent filters option:

From the new Consulting Services resource on the Help ribbon, you can explore a wide variety of curated fixed price, fixed scope, and fixed duration consulting services from our vibrant partner community. These affordable packaged briefings, assessments, workshops, proof-of-concept, and implementation offerings can help you get started with Power BI, take your skills to the next level, or help set you on the path to solve difficult business problems. 

Watch the following video to learn more about the Consulting Services resource:

That’s all for this month! We hope that you enjoy these updates and continue sending us your feedback. Please don’t forget to vote for other features that you’d like to see in the Power BI Desktop.

5d7f8edd 60a2 45e6 8cd8 5a7fa35e97b5 Power BI Desktop February Feature Summary

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Connect to tens of thousands of datasets on data.world with the new connector for Power BI Desktop (and join the webinar!)

Microsoft Power BI strives to connect you to the data that matters most to you, whether that’s Excel spreadsheets, on-premises data sources, cloud services or even web tables. Late last year as part of the December Power BI Desktop release, we launched a new connector for data.world – aka the social network for data nerds.

data.world is a platform where people can find, use, share and collaborate with a vast array of high-quality open data. The platform is secure, respecting privacy of sensitive data, so that users can decide to share their data publicly or privately with select users under their control. Think of data.world like a social network, where you can converse, add context to data and collaborate with others (but only if you want to).

With access to tens of thousands of open datasets spanning all sorts of subject matter, the new connector helps you go from data to insights in minutes. And you’ll never have to worry about having the latest and greatest data, because the data connector syncs directly with data.world, so that you’re always using the freshest data.

Join us for the upcoming webinar on February 22nd that explains more about this! Skip ahead to the webinar registration here.

Getting started with the data.world connector

Sign-up or sign-in to data.world

First, you will need to sign up for a data.world account (free or paid) to use the connector in Power BI. Once you have a data.world account, you can browse the site for interesting data sets.

When you’ve located a data set you’d like to explore or analyze with Power BI, take note of its URL, which is comprised of an Owner and Dataset ID.

Example:

The Foreign Exchange Rates data set published by the Associated Press has a URL that looks like this:

https://data.world/associatedpress/foreign-exchange-rates

In this case, the Owner would be associatedpress, and Dataset ID would be foreign-exchange-rates.

 

Launch Power BI Desktop

Note: the connector supports Power BI Desktop December 2017 release or greater. Don’t have it yet? Download Power BI Desktop for free here.

1. Open Power BI Desktop.

2. From the Get Data dialog, select Online Services, and then Data.World – Get Dataset (Beta).

08d241e2 acac 48cf 848d f1dcd0a7da12 Connect to tens of thousands of datasets on data.world with the new connector for Power BI Desktop (and join the webinar!)

3. On the connector prompt, enter the Owner and Dataset ID values from data.world (outlined above) and click OK.

f1f795a4 6d0e 4338 8419 c7c42b063661 Connect to tens of thousands of datasets on data.world with the new connector for Power BI Desktop (and join the webinar!)

4. The first time you connect to data.world, you will be prompted to authenticate with the service. The connector provides two authentication options – OAuth v2 (recommended) and API Token.

ddd26825 cc63 487c a291 7128defc879d Connect to tens of thousands of datasets on data.world with the new connector for Power BI Desktop (and join the webinar!)

a. Using OAuth v2 (recommended)

By clicking the Sign in button, a dialog box will pop up asking you to enter your data.world credentials and authorize the Power BI application.

1e933e66 5bee 407a 805f 1c800e6946b2 Connect to tens of thousands of datasets on data.world with the new connector for Power BI Desktop (and join the webinar!)

b. Using API Token

This authentication method uses a fixed API key associated with your account, which you can find on the data.world site. You can locate your API Token on the Advanced page of your Profile Settings in data.world. Copy and paste your Read/Write token into the Account Key dialog in Power BI.

1f45c5ef df6f 4c6f 8987 6016ef7965bc Connect to tens of thousands of datasets on data.world with the new connector for Power BI Desktop (and join the webinar!)

 

Load data

5. After providing your data.world credentials, the Navigator window will list all tables/files within the dataset. Selecting an entry will display a preview, which you can load into your Power BI model, or click Edit to bring up the Power Query Editor to further refine and shape the data.

fe10304d a351 4fdf 8438 7c414ff88de7 Connect to tens of thousands of datasets on data.world with the new connector for Power BI Desktop (and join the webinar!)

For example, the Foreign Exchange Rates data set is pivoted, which is not ideal for analytics. By editing the query, you can quickly unpivot the data and add some simple date calculations, leveraging Power BI’s built-in support for Date/Time visualizations.

549e375d 463a 4bd3 b55b 65cfdb6d95e8 Connect to tens of thousands of datasets on data.world with the new connector for Power BI Desktop (and join the webinar!)

 

Clean, model, visualize and analyze the data set

From here, it’s business as usual with Power BI Desktop. You can clean, model, visualize, and explore your dataset. Check out the Power BI Guided Learning resources for more.

Publish your report to the Power BI service

Once you have built your Power BI report, you can publish it online, and even add it to your blog or website. Publishing your report to the online Power BI service will allow you to configure a scheduled refresh for your data.world queries.

To publish to the Power BI service, follow these instructions. If you don’t already have a Power BI service account, you’ll need to create one (it’s free to sign up).

After publishing your report to the Power BI online service, you can configure scheduled refresh for data.world queries. Check out the Configuring scheduled refresh page for more information.

23186936 5767 4a8f 9465 ceb5474b8262 Connect to tens of thousands of datasets on data.world with the new connector for Power BI Desktop (and join the webinar!)

Want to share your report on the web?

Once your report is published in the Power BI service, you will have the option to make it public. When you publish your report, it will retain all its interactive functionality so users can still filter and interact with the visualizations. It can also be embedded into your website with the generated embed code that can be resized.

Follow these instructions to learn how to create this embed code and publish the report to the web.

Bonus! If you want to share your insights back to the data.world community, you can use the Power BI report URL to add to a project on data.world.

Interested in learning more?

Join us for a webinar on February 22, 2018 at 10am Pacific. Register here.

More resources

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

How to Connect One Slicer to All Your Power BI Report Pages

And another one bites the dust… this month the Power BI team have only delivered the #1 most requested feature on the Power BI Ideas forum – synced slicers. This functionality allows you to apply a user’s selected filter criteria throughout the entire report. If a user wants the 2018 Q1 report, they need only choose those filter criteria once. Previously it may have been necessary for the user to repeat this on every page, which was error-prone and confusing.

2018 02 05 21 15 42 Power BI Ideas  Top 11026 ideas %E2%80%93 Microsoft Power BI thumb How to Connect One Slicer to All Your Power BI Report Pages

Another new feature added was cross-filtering across multiple visuals. Ever found yourself in that situation when on a minute’s notice, you suddenly have the attention of the CEO. They give you thirty seconds to engage in a little data exploration and feed you a chain of 5 or 6 questions that only occur to them as you slice & segment your way through the first few layers of questions. You get to a 3rd of 4th filter criteria, try to control-click that pie chart the CEO is pointing at and have to turn and say “uh I can’t click that, we’ll lose our filters.” It wasn’t a deal breaker, but it was a buzzkill. Well, that’s been fixed too. Control-click pretty much does what you want it to now!

Between these two features, I’m sure you figured it was going to be great for preserving screen real estate. With no need to have duplicated slicers on every single page, we can free up 10-20% of the canvas on most of the reports for larger and more engaging visuals, right? I figured I would set up a home page with all the slicers there and I’d have MORE CANVAS! Well yes, I certainly did all of that and you will too, but it quickly becomes apparent that it’s not quite as simple as that.

If you tuned in last week, you’d have caught Reid’s post on SELECTEDVALUES that began with the ominous headline

Data Ambiguity…The Silent Report Killer.

With our newfound powers to minimize slicers, these are words worth remembering. The techniques covered in that post are going to be fundamental to report building going forward (this one and this one too) as we’ll discover today.

How to Use the Sync Slicers Functionality

First of all, how does it work? To work with Slicer syncing, you’ll first need to expose the Sync Slicers pane. To do this, go to the View menu and check the Sync Slicers box.

2018 02 06 00 30 52 Selected Values Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

You’ll receive some pretty clear instructions guiding you to select one of your slicers. Slicer sync is only going to work with the actual built-in Slicer visualization. Syncing across pages does not yet work with any of the customer visual slicers like the Chiclet Slicer or the other visualizations like the column or pie visualizations.

2018 02 06 00 36 38 Selected Values Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

Once you activate a slicer, you may see something like this if you had common slicers already existing on each page.

2018 02 06 00 49 58 Selected Values Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

The first column is the list of pages in my workbook.

The second column is the ‘sync’ column, and as none of these are checked, the slicers will continue to function independently of each other. If I check this, the slicers are essentially linked, and a change on one page occurs on the other.

The third column indicates whether the slicer is displayed. As this was an existing report where I had a report category slicer on each page, it makes sense that all three are checked. But I no longer need to keep these visible to the end user. So I can uncheck these. The slicer does need to exist on that page physically; it just doesn’t need to be visible.

As my first test, I went through and checked all of the sync checkboxes and sure enough, if I selected a product category on one page, that selection ‘stayed’ when I moved on to the next page. Four simple clicks.

One Page To Rule Them All

Buoyed with confidence, I jumped right into setting up a Home page where I just copied, pasted and reformatted all of the existing slicers. We use the drop-down slicer a lot, usually because it is efficient regarding preserving screen real estate when inactive, but with all this space, I went all out with lists, sliders, image slicers! If we wanted to get crazy, we might even want to increase the font size! Too much?

2018 02 06 18 00 33 Sync Slicers Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

See that giant image slicer on the right-hand side? Those are our nine employees. This was an entirely new slicer, and when I selected this slicer and went to the Sync Slicers pane, it looked like this.

2018 02 06 00 49 00 Selected Values Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

While the Employee Name field had been used in Matrix visualizations on other pages, it had not been used in any Slicers. I wondered why then those other pages displayed the option to sync at all and weren’t grayed out. How could the slicer on my Control page sync with the other two pages, when I knew that no slicer for that field existed on those fields?

So I just I checked all three and then went ahead and selected an Employee from the slicer.

2018 02 06 17 58 40 Sync Slicers Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

And the Company Report is showing the same sales figures as my control page!

Great – so it just works? Well yes and no. When I selected the sync button, it added an entirely new slicer to the other two reports but left them hidden.

2018 02 07 19 54 38 Sync Slicers Report Power BI Desktop thumb How to Connect One Slicer to All Your Power BI Report Pages

If we make the new slicer visible either by clicking the eye icon in the Selection Pane or by going back to the Sync Slicers pane and checking the boxes in the last column, then we can see that Power BI has duplicated the original slicer onto the new page, formatting, position and all. I think you’ll agree that it is probably best left hidden in this case.

2018 02 06 18 49 21 Sync Slicers Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

So now all that is left is to go through each one of the slicers on my home page and ensure all of the sync checkboxes are checked for all of the pages. Then I have universals slicers regardless of where I choose to display them.

2018 02 06 19 02 10 Sync Slicers Report Power BI Desktop thumb 1 How to Connect One Slicer to All Your Power BI Report Pages

Surely now with everything synced and a complete slicer page, I can get rid of those slicers on the report pages? Let’s hide those and make the most of all this extra space…

2018 02 06 14 56 04 Sync Slicers Report Power BI Desktop thumb 1 How to Connect One Slicer to All Your Power BI Report Pages

Goodbye slicers – hello ambiguity! The above chart looks crisp, but what am I looking at? The numbers changed… did another slicer sneak in somehow?

With slicers now absent from the report pages likely to become a more common occurrence, we should now always be asking – will it be apparent to a user what they are looking at? If they get interrupted and have to come back to their data deep dive, how will they recalibrate?

It seems no coincidence that SELECTEDVALUE was introduced only a few months before all of the storytelling features began rolling out. DAX is still a huge part of using these features.

timeline thumb How to Connect One Slicer to All Your Power BI Report Pages

Every model using Slicer Sync should be using multiple SELECTEDVALUES or CONCATENATEX measures. So get comfortable with these DAX functions before implementing slicer sync….. or at least get used to using the Concatenated List of Values Quick Measure.

In the absence of visible slicers on every page, these slicer selections will need to be displayed somewhere on the page as Reid outlined last week. I’ve added mine down the left-hand side where the slicers used to be.

2018 02 06 19 29 55 Sync Slicers Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

So this doesn’t seem like a huge enough win – I’ve only replaced the slicers themselves with a list of filter selections. But… I have centralized the slicer selections (sort of), so that is absolutely a real benefit regarding user experience. At least a user can be confident that they can make their selections and move on.

Bookmark Bill Banner Image thumb 1 How to Connect One Slicer to All Your Power BI Report Pages

Not satisfied with this and a little weirded out by all these hidden slicers necessarily lurking beneath the surface of my report, I turned to Bookmark Bill for some advice.

He suggested that an alternative to creating a landing page would be just to simulate that landing page within every page, allowing the user to flip between a chart view and a filter selection view while remaining on the same sheet… using, you guessed it bookmarks!

The slicers are already there on every page, why not at least provide the option to use them?

1. Add two icons to which we will bind the bookmarks – one for a charts view and one for slicer selection view.

2018 02 06 16 14 37 Sync Slicers Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

2. Go to View, and check the Bookmarks Pane checkbox if it is not already checked. Then add the bookmark and name it Chart View.

2018 02 06 16 25 49 Sync Slicers Report Power BI Desktop thumb How to Connect One Slicer to All Your Power BI Report Pages

3. Go to View, and check the Selection Pane checkbox if it is not checked already. Turn off all of the elements that you do not wish to display in Slicer View.

2018 02 06 16 23 39 Sync Slicers Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

4. Now make all the slicers visible, format and lay them out as you would want the Slicer View to appear. Add another bookmark and call it  Slicer View. With the excess space, I have available I’ve even left a line chart on the to give the user the high-level overview of the results of what they are selecting.

2018 02 06 19 45 52 Sync Slicers Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

5. Now we have two bookmarked views that we need to bind to our two images.  Select the chart image, go to the Formatting pane on the right, then to the Link submenu and change the Link Type to Bookmark and the Bookmark to bind to the Chart View.  Repeat the same process for the Slicer image and the Slicer View, and there’s only one more thing to address.

2018 02 06 16 40 29 Sync Slicers Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

6. By default bookmarks are tied to a set of filter criteria, however, as of the December update, this can be turned off so that the universal slicer selections made by the user will remain.  On the Bookmarks pane, click the ellipsis next to each of the two bookmarks and uncheck Data.

2018 02 06 16 44 19 Sync Slicers Report Power BI Desktop thumb 2 How to Connect One Slicer to All Your Power BI Report Pages

Now you have given the user base options. They can return to the Home page to change filter selections or just stay on the page they are on. Regardless of where they are making their selections in the report, their slicer selections should permeate uniformly.

I’m looking forward to seeing what our team and the Power BI community come up with to take advantage of these features while creatively tackling the challenge of potential data ambiguity.

There is an obvious benefit to these latest changes, so, by all means, consolidate your slicers, but always beware the silent report killer….

backstabber sequence thumb How to Connect One Slicer to All Your Power BI Report Pages

It takes a special kind of mindset to “bend” data (and software!) to the human will.  As this article demonstrates, we at PowerPivotPro can twist Power BI into a pretzel if that’s what an organization needs. (A robust, trustworthy, industrial-strength pretzel of course).

The data-oriented challenges facing your business require BOTH a nimble toolset like Power BI AND a nimble mindset to go with it. And as Val Kilmer / Doc Holladay once said, we’re your huckleberry.

Connect with the Experts

Let’s block ads! (Why?)

PowerPivotPro

Power BI Service and Mobile January Feature Summary

The Power BI service and mobile team is thrilled to kick off the first feature summary blog for 2018! With a new year in full swing, we’ve been hard at work on features to make it easier for you to achieve more with your data. In January, we released some highly anticipated features in the Power BI service and mobile apps. Here’s a quick recap in case you missed it:

Dashboards and reports provide two distinct ways of presenting information in Power BI, each with their own advantages. Since we launched the product, dashboards have been the primary way to share content with others in the service. However, there has always been an overwhelming ask from our community and users to extend the same capability to reports – and that’s exactly what we delivered in January. Yes, you read that right – you can now sharereports directly with others in Power BI!

The feature works just like dashboard sharing. Simply head to any report, look for the “Share” option in the top bar, and select it to launch into a familiar pane experience where you can select the recipients that you want to share the report with and choose to give them re-share permissions.

7520df47 eab4 4ac8 90a1 29c2ca60db74 Power BI Service and Mobile January Feature Summary

After you share a report, it will appear in their “Shared with me” page and receive an e-mail if you’ve selected it.

In addition, we have also made it easy for you to quickly find and access Power BI content by allowing you to favorite reports, paralleling the functionality in existing dashboards. Just select the “Favorite” option with the star on the top bar of a report, and it should immediately appear in your Favorites content list. Learn more

It doesn’t stop there. You can also access reports that are shared with you on to go using your Power BI mobile app and even favorite them for quick access!

Starting in January, dataset owners can choose to use AAD OAuth credentials when connecting the Power BI service to Azure SQL databases. With this update, Power BI can leverage all the security features of AAD when connecting to Azure SQL – including multi-factor authentication, when its configured. The OAuth authentication method is supported when connecting for both import and DirectQuery. In addition, when connecting to these data sources via DirectQuery, the Power BI service can now be configured to connect to the data source with the end users’ credentials, allowing the service to respect the security settings configured at the data source level. Learn more

We’re pleased to announce that Power BI customers around the globe who enable auditing in their tenant will now have these audit logs stored in the same Office 365 region as their tenant.  This region is determined for each tenant, based on the country selected during initial signup for the first Microsoft service in the tenant, such as Power BI or Office 365. Specific audit log locations can be found in the Office 365 trust center by finding the Exchange Online locations based on your tenant country. Another benefit of this change to how audit logs are handled is the data will be available much sooner than it was previously, where customers would often not see events in their audit logs for up to 24 hours after they originally occurred.  Now, these same events will be available within an hour or so, and often times even sooner.

We are excited to announce the general availability of embedding interactive Power BI reports in SharePoint Online. This feature enables SharePoint authors to easily embed Power BI reports directly on their page without any code! Since the introduction of this feature almost a year ago, we have made significant improvements based on your feedback to ensure reliability and performance.

c74b4003 ded1 436d a384 63c38388d6ef Power BI Service and Mobile January Feature Summary

To learn how to use the Power BI web part, see Embed with report web part in SharePoint Online on the Power BI documentation site.

We are giving IT admins more control over whether users in their organization can  view reports containing custom visuals. Please note that the setting cannot be limited to specific groups and applies to the entire organization. Learn more

671043a1 8708 420b a3bb 3b34c90ca893 Power BI Service and Mobile January Feature Summary

Power BI has had seamless connectivity to Azure Analysis Services since it was first launched. However, we only supported live connections – until now. We are excited to announce that Power BI now supports imported data from Azure Analysis Services and for the scheduling of those imports to be refreshed.

We continued to make enhancements and shipped a new update for the Power BI On-premises data gateway in January. The update includes a beta release of the PowerShell Cmdlets to manage on-premises data gateway in the public PowerShell gallery, configuration that lets you control the number of mashup engine containers that can run in parallel, and a new version of the Mashup Engine. Try it out for yourself by installing the new gateway and continue to send us feedback on any new capabilities you’d like to see in the future.

We’ve continued to improve your mobile experience by now allowing you to directly navigate to links within custom visuals and open them on your favorite mobile browser.

Resources

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Power Query (M)agic: Right-Click Your Way To Data Cleaning Happiness

As a ‘water cooler’ of sorts for this community, we meet some amazing people. Doug Burke shares with us how easy Power Query makes it to do powerful cleaning with a click of the mouse.

The Problem

If you’re like me, you get crappy data from your source systems.  Not that the data is inaccurate. No. It’s just that the data does not fit your view of the world. It doesn’t suit your needs right out of the box. That makes it crappy.

You need to clean it to make it usable. You need the ‘power’ of Power Query to turn night into day.

Did you know that Power Query’s most potent data cleanups are just a right-click away? Let me share the secret.

It’s a simple three-step process:

1. Import data into Power Query

2. Right-click a column

3. Select a data cleaning function

Let’s take a look . . .

image thumb Power Query (M)agic: Right Click Your Way To Data Cleaning Happiness

Try it yourself. Import some data. Right-click a column. Look at all those functions.

I count 42 data cleaning possibilities just from what’s visible. That doesn’t even include the additional transformation powers of ‘Group By,’ ‘Add Column From Examples’ and ‘Add as New Query.’

A whole lot of something is going on here.

Let’s start with a simple example. We’ll use right-click-column to fix inconsistent customer names.

• Customer XYZ sometimes is listed as XY&Z. We need to remove the ‘&’ symbol.

image thumb 114 Power Query (M)agic: Right Click Your Way To Data Cleaning Happiness

image thumb 115 Power Query (M)agic: Right Click Your Way To Data Cleaning Happiness

image thumb 1 Power Query (M)agic: Right Click Your Way To Data Cleaning Happiness

Your first reaction may be ‘So what. I can do the same thing with Excel’s Search and Replace’. True, but you would miss out on Power Query’s creating a series of ‘Applied Steps’ that are consistent, repeatable and set you up for more data transformations within the Power Query Editor.

Another example, this time using two columns. Find the unique instances of products bought by customers. It’s not as simple as it sounds because different customers purchase different products. You want to quickly see the unique combinations of which customers bought what products and exclude the duplicates.

image thumb 117 Power Query (M)agic: Right Click Your Way To Data Cleaning Happiness

image thumb 118 Power Query (M)agic: Right Click Your Way To Data Cleaning Happiness

image thumb 119 Power Query (M)agic: Right Click Your Way To Data Cleaning Happiness

A quick review of using right-click column data cleanups:

  • Consistent
  • Repeatable
  • Sets you up for more data transformations within the Power Query Editor

How about one more example to drive the point home? This time, let’s fill in missing customer names. This is a frequent problem if your source data is an existing formatted report which excludes repeating values. Power Query can handle it in a snap.

image thumb 120 Power Query (M)agic: Right Click Your Way To Data Cleaning Happiness

image thumb 121 Power Query (M)agic: Right Click Your Way To Data Cleaning Happiness

image thumb 122 Power Query (M)agic: Right Click Your Way To Data Cleaning Happiness

‘Right-click-column’ is a fast, convenient way to get the job done. No need to review the Power Query ribbons for column cleanups functions. There are so many readily-available combinations that it boggles my mind.

Bonus: when done, you have a sequenced workflow (know as ‘Applied Steps’) that can be repeated the next time you get data.

Simple, right? That’s the point – – Power Query makes it simple. Right-click-column makes it simpler! Win-win!!

I am not saying this is the onlyway to clean up data. Or even the best way. But I am saying it’s a quick way. And sometimes its better to be quick than good.

Full disclosure: I’m using Excel 2013 Professional Plus 64-bit (2.49.4831.381). Your mileage may vary depending on your version of Excel and Power Query.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.

Let’s block ads! (Why?)

PowerPivotPro

Using SELECTEDVALUES To Capture Power BI Slicer Selections

Hello P3 Nation, I’m excited to be back and writing a technical post again…it’s been too LONG! Today I want to talk to you about a real-worldbusiness problem and solution I encountered last year. I think it’s a scenario at least a few of you will relate to. I was working on a project for a client, and needed to create a Power BI report for them.

However, the client didn’t think the active filters in the report were easy to identify. Now this is a scenario I’ve encountered a few times over the years, whether it’s been in Power BI, Excel, or other reporting tools. Overall it comes down to this, data ambiguity can render a report A) Untrustworthy, B) Dangerous, or C) Useless. Personally, I think a well-designed Power BI report has clear “enough” call-outs on the filters. Truth be told though, it only partially matters what we think. At the end of the day, we need to cater the report to the client’s needs

Typical report layout I create. Primary sections for – Headers, Slicers, Visuals/Tables:

Power BI Report Before Filter Cards thumb Using SELECTEDVALUES To Capture Power BI Slicer Selections

The client and I brainstormed, and we decided to create card visuals to identify filter selections. The beautiful thing about DAX, as mentioned in many articles on our site, is that it can easily return text values. I actually did something similar to this with Dynamic Titles when I posted about Power BI’s new Drill Through feature last year, that article can be found here. So, I essentially wanted to do something similar here, but to call out the filter selection for each slicer. The end result looked something like this

Same report with additional section for slicer filter selection:

Power BI Report After Filter Cards thumb Using SELECTEDVALUES To Capture Power BI Slicer Selections

The end result was a new section of the report, dedicated to calling out slicer selections. The BIGGEST reason the client wanted this, was for screenshots. They often took screenshots of this report, and pasted it into emails or slides to use in presentations. The result works well, and uses a bit of clever DAX to always return the right selections, no matter the combination of selections among the slicers.

To get the right output with DAX I utilized one of the newer functions, Selected Values. Selected Values is used whenever you use the What If feature in Power BI Desktop. In fact…if you look at my screenshots above, the Rolling Avg Months & Expense RatioWhat Ifs are attached to Selected Values measures. Selected Values works like this, you give it a column reference, let’s say [Column A]. If there’s a selected value (E.g. single selection) on that column, it returns that value. Otherwise there’s an alternative result returned. Here’s what the DAX Measure looks like for the Expense Ratio Value.

     Expense Ratio Value = SELECTEDVALUE(‘Expense Ratio'[Expense Ratio], 0.50)

Selected Values is a fairly simple, and straight forward function. For a thorough and in-depth article on it’s uses and abilities, I’ll refer you to a post Marco Russo wrote over at SQLBI. I figured I could use this function to point to my slicer columns, and return a “No Selection” alternative result if there wasn’t. I thought that’s all I’d need, just rinse and repeat a bunch of Selected Values measures, and I’d be done! Not quite…I noticed a few issues after I did this.

I quickly realized that I was getting false positive filter selections when using certain slicers. As an example, whenever I made a specific selection on Employee Sales Region, sometimes the filter selection for Employee Team Name would change too! What was happening is there was a Sales Region with only a SINGLE Team name. It was triggering the Selected Value logic, because only a SINGLE VALUE was being given to it.

False positive filter selection, despite no selection on the slicer:

Power BI ISFILTERED Problem thumb Using SELECTEDVALUES To Capture Power BI Slicer Selections

I needed my DAX measure to run ONLY when there’s an ACTIVE filter on the column. Selected Values doesn’t check to see if the column has an active filter on it, instead it’s only checking to see if there’s a single value from that column. Some additional business logic would need to be applied to it, to FIRST check for active filtering BEFORE running the Selected Values logic. I worked through a few variations, and eventually ended up using the DAX Function ISFILTERED.

     Employee Sales Region Selection = IF( ISFILTERED(‘Employee Details'[Sales Region]), SELECTEDVALUE(‘Employee Details'[Sales Region], “No Selection”), “No Selection”)

ISFILTERED worked, and I no longer got a false positive filter selection! However, further testing unveiled a second problem. The other issue aired its ugly head whenever a slicer had multiple selections on it. Now my DAX is returning a false negative! I want it to return “Multiple Selections” if I have TWO or more slicer selections. This will be an easy enough fix actually.

The Employee Sales Region Selection DAX (above) has two conditions it’s checking.Condition one (ISFILTERED) is checking if there’s an ACTIVE filter on the column. If that condition is met, then it checks condition two (SELECTEDVALUES), which checks to see if there’s only ONE value coming from that column. If that second condition isn’t met, it returns “No Selection” at the inner most part of the query. All we have to do is update the alternative result in condition two, and change it to “Multiple Selections“.

     Employee Sales Region Selection = IF( ISFILTERED(‘Employee Details'[Sales Region]), SELECTEDVALUE(‘Employee Details'[Sales Region], “Multiple Selections”), “No Selection”)

BEFORE – Multiple selections on the slicer, but no indication of filters on the card:

Power BI Multiple Selection Problem thumb Using SELECTEDVALUES To Capture Power BI Slicer Selections

AFTER – Multiple selections on the slicer, Multiple Selections indicator on card:

Power BI Multiple Selection Solution thumb Using SELECTEDVALUES To Capture Power BI Slicer Selections

It’s also possible to return the entire list of selected values, instead of “Multiple Selections”. In fact, Rob has a great post on how to do JUST THAT. It utilizes a DAX Function called CONCATENATEX, and I’d recommend giving it a read. I didn’t go that route, as the client was happy just having multiple selections as the output for this scenario. There you have it folks, a great way to add a bit of clarity to your report. In case youever need a really obvious call-out for your slicer selections. I’m glad to be back in the technical saddle a bit on post writing, look forward to some more posts from me this spring!

Let’s block ads! (Why?)

PowerPivotPro

Why Content Marketing and PPC Are the Laser-Targeted Power Duo

20180131 bnr dual lasers 351x200 Why Content Marketing and PPC Are the Laser Targeted Power Duo

Do a careful read of the article and ask yourself, “What could be improved on this topic?” Maybe there are gaps in the existing article, or things that aren’t covered well ― or covered at all. Maybe the content would benefit from interviews, research, or additional statistics. For example, for a trends article, you could interview several industry influencers and use their quotes to support each trend. Or perhaps you could infuse the article with videos to add depth and insight to the subject. Take a topic that has performed well and use this knowledge to create something better by adding more of what the reader wants.

You can also tap into the content that you’ve already published through your own content marketing efforts. You may have a high-performing e-book that you’d like to get into the hands of more prospects. If so, you can use a PPC strategy to accomplish that (more on that in a minute). But first, here are a few tips for making content even better, whether it’s a new piece or an existing asset that you’re refreshing:

  • Create twists and turns to capture interest. Has your topic been covered excessively? If so, it doesn’t mean that you shouldn’t write about it, especially if the topic has performed exceptionally well in the past, but the key is to stand out. Try doing the opposite of what others are doing. For example, if everybody is writing about the best strategies for XYZ, write about the worst ones (and how to fix them).
  • Capture attention with amazing headlines.Eighty percent of people will read your headlines, but only 20 percent will read your content. So, you can never spend enough time perfecting your headlines and checking their results. PPC is an excellent tool for testing your headlines and determining which works best.
  • Create content that is actionable. Providing strategy is great, but what can your readers do with your content? Can they take what they learned and apply it right away? Actionable content empowers your readers and builds greater engagement and brand loyalty.
  • Add images and video to your content. Video is expected to claim more than 80 percent of all traffic by 2019, and 90 percent of customers report that product videos help them make purchasing decisions. If you’re not using video in your content, brainstorm ways to add it. For example, in the 2017 content marketing trends example above, you could include short video clips of the influencers giving their predictions, and then test the results through PPC campaigns.

You’ve created amazing content that is proven to perform well because you’ve invested time and research in finding the best topics. You’ve reviewed existing high-performing content, found the gaps ― and you know exactly how to make it better. So now what? If you want to get truly amazing results, couple that amazing content with a PPC strategy.

Combining Your Content Marketing and PPC Strategies

Using content marketing and PPC together works very well because you’re taking a piece of powerful, popular content and sharing it with people through PPC ads. This instantly boosts the odds that you’ll get a higher ROI on your marketing dollars. But first, you must understand the most effective place to invest your content and PPC dollars. Here are a few tips for getting started.

– Understand where customers spent time on social media. Measure the engagement that you receive from different social media platforms. For example, a B2B technology company notices it gets a high level of engagement through LinkedIn. If so, this is the first platform where you should test content-driven PPC. Measure your results, and iterate as needed.

– Determine the best content subject and format to promote it. Gated assets, such as that great e-book or detailed white paper that you wrote about a major pain point for the audience, are ideal candidates for PPC. Seasonal content, such as that trends article launched at the end of the year, are also good choices. Take the assets with a track record of excellent performance and test those first.

– Segment your campaigns. Some companies make the mistake of setting up campaigns for each of their products or services. Instead, plan campaigns for as many groups as possible, and segment by the main product feature, benefit, or other specific pertinent factor for your target audience.

– Leverage relevant keywords. The more specific your campaigns are, including keywords, the better your leads will be. Obsess over searcher intent.

For past content marketing efforts, you may have used guest posting, which is an effective strategy for getting in front of a target audience. The challenge, however, is that only a fraction of the total readers will be interested in your product. In contrast, leveraging content through PPC campaigns allows you to drill down to a specific persona that you’d like to target, so it’s no longer a numbers game.

For example, LinkedIn allows you to create a sponsored content campaign by using the campaign manager feature and then clicking the “sponsored content campaign” option. Simply select the piece of content that you want to sponsor. NewsCred used this strategy with LinkedIn and increased views 4.2 times for their unsponsored company updates, after running 20 sponsored update campaigns. Use examples like these to inspire your efforts.

Altering the Buying Path for Greater Results

Customers are traveling a buying path, and it may be long or it may be short. They may have some awareness of your product ― perhaps they saw it at a trade show or a co-worker mentioned it ― but they’re not even close to purchase yet. Combining content marketing with PPC allows you to alter the course of this path.

Whether prospective customers are at the start of their search or at the end of the sales cycle, you can move them forward faster and closer to the sale with this powerful double-whammy strategy.

Have you used PPC to increase the effectiveness of your content- marketing efforts? If so, please share what you’ve learned.

Let’s block ads! (Why?)

Act-On Blog