Published Date : May 17, 2016
Power BI is a cloud service, and that means Power BI files are hosted somewhere. Some DAX functions such as Date/Time functions work on system date/time on the server their file is hosted on. So If you use DAX functions such as TODAY() or NOW() you will not get your local date/time, You will fetch server’s date/time. In this blog post I’ll explain methods of solving this issue, so you could use Power BI to resolve your specific time zone’s date and time. If you want to learn more about Power BI read Power BI online book; Power BI from Rookie to Rock Star.
Using DAX functions on your local Power BI file is totally different from what you will see in Power BI website especially when date and time functions has been used. The reason is that DAX works with the date and time of the system that hosted the Power BI file. Power BI is a could based service, and that means Power BI files will be hosted on a server somewhere in the world, that might not be on the same time zone as your city is. So as a result when you used functions that works with the current date and time; such as TODAY() or NOW() in DAX you will not get your local current date and time. At this stage there is time zone feature in DAX functions to help resolving this, so I suggest few options to resolve it as below.
Screenshot below shows a Power BI report published on Power BI website, and the result of DAX NOW() function there compared with the local date/time on the client system. Please note that you won’t see this anomaly in Power BI Desktop, because in that case file is running on your local system, and the result would be your local date/time, you will only face this issue when you publish solution to Power BI website.
One easy way of solving this is to add time offset to the date/time function in DAX. Power BI date/time seems to be GMT. So if I want to show my local time in Auckland, I have to add 12 hours to it. Or for Seattle I have to reduce 7 hours from it.
So I create a new calculation as DAX NZ TIME with this code:
DAX NZ TIME = NOW()+(12/24)
and another for DAX Seattle Time with this code:
DAX Seattle TIME = NOW()-(7/24)
Here is corrected result as below;
This method works but has an issue which I deal with it later on.
Thanks to my friend Ken Puls who mentioned this method to me in PASS BA conference, I come with this second option. Fortunately in Power Query there is set of functions for DateTimeZone. Ken already has a blog post about time zones with Power Query which is a good read and recommended. DateTimeZone functions has options such as fetching local time or switching time zones. For this purpose I can use DateTimeZone.SwitchZone function to switch server’s local time to my time zone’s date and time.
= DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),12,0)
12 is hours, and 0 is minutes for the new time zone. script above will turn the local time zone to NZ time. for turning that into Seattle time I have to set parameters to -7, and 0.
And here is the result set:
You can also use other functions such as DateTime.AddZone in Power Query to turn the local date time to specific time zone.
Well above solution works like DAX method, but both suffer from similar issue; Day Light Saving Time. This is the reason that if you try code above in summer or winter you might get different result!
Day Light Saving is a big challenge, because each time zone, city, or country might have different day light saving time. even same city might have different dates for DST (Daylight Saving Time) for different years! Power Query is intelligence enough to help with Time Zone issue, but doesn’t have a directory of all DST times for all time zones. Fortunately Power Query can query web URL. And there are some websites that give you the current date and time for your specific city, country, or time zone. And those websites usually consider DST correctly. One of these websites is TimeAndDate.com . As you see in screenshot below this website gives me the current date and time for most of cities around the world;
In Power Query we can use functions such as Web.Page() and Web.Contents() to read tables in a web page, and then fetch part of it that we want with some other transformations. I won’t be explaining details of using timeanddate.com URL to fetch the local city here because it would make this post very long. I just refer you to my other post about reading some date/time information for different time zones which is similar to method I’ve used here. If you want to understand how code below works read the post here. For this part I will be using another website which gives me current date and time in Auckland, and here is Power Query code:
let
Source = Web.Page(Web.Contents("http://localtimes.info/Oceania/New_Zealand/Auckland/")),
Data1 = Source{1}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}}),
date = #"Changed Type"{1}[Column2],
time=#"Changed Type"{0}[Column2],
datetime=DateTime.FromText(date&" "&time)
in
datetime
And here is the result with the correct DST and time zone;
At the time of writing this post, I’ve only thought about these three methods. You might have an idea about another method. In that case, don’t hesitate to share it here.
Published Date : May 16, 2016
Previously You have seen in Part 1 and Part 2 of this series, that how you can use Power Query to do data transformation and turn the result of event date/time scheduler of timeanddate.com website to a better format. We’ve used number of basic transformations, and some functions such as Table.Transpose to fetch desired output. This part is the last step which we will use parameters to make our query dynamic. At the end of this part you will learn how to automate your Power Query solution, with a change in parameters in an Excel table, and hit refresh button a new result set with desired format will be created. To learn more about Power Query, read my Power BI online book; Power BI from Rookie to Rock Star.
We’ve made all the data manipulation and mash up, and the output result set is exactly as desired. However, one step is left: Using Parameters and making this query dynamic or automate the process. For parameters I use an Excel table as my configuration table. So I create a table as below;
This table has three columns: Date, Time, and Duration. I separated the date and time for simplicity of this example. Date to be formatted as YYYYMMDD, and Time as HHMM, and duration as an integer value illustrating hours.
Configuration above means the event starts at 9th of May 2016, at 1:00 pm New Zealand time (this is what my local time is), with duration of 3 hours. I named this table as InputData.
I can fetch this table in my Query Editor with a simple M script line as below;
input = Excel.CurrentWorkbook(){[Name="InputData"]}[Content]
Excel.CurrentWorkbook reads a table from the current excel workbook. Name of the table passed as input parameter. And [Content] will load content field of the table (which is a table by itself of all columns and rows of the Excel table) into the variable named input.
I can then read first record of this table into a variable called inputrecord. And use it for fetching each parameter later on. Here is my inputrecord
inputrecord=Table.First(input),
Now I can read Date value easily by expression below:
date=Text.From(inputrecord[Date]),
For Time I do also use a PadStart with 0 to generate values like 0400 when time is 4AM. The reason is that TimeandDate.com asks for a full HHMM string with leading 0 to work correctly. Duration calculation is simple as well.
time=Text.PadStart(Text.From(inputrecord[Time]),4,"0"), duration=Text.From(inputrecord[Duration]),
Now that I have all three parameters in variables named; date, time, and duration. I can simply use them in generating web url to pass to timeanddate.com website as below;
WebURL="http://www.timeanddate.com/worldclock/fixedtime.html?msg=Power+BI+Training&iso="&date&"T"&time&"&p1=22&ah="&duration,
String concatenation character in Power Query is: &. and expression above generates the web url for the date/time of event with local time of Auckland, New Zealand with duration specified. All I have to do now is to fetch data from this URL with the WebURL variable. So I’ll change line below;
Source1 = Web.Page(Web.Contents(“http://www.timeanddate.com/worldclock/fixedtime.html?msg=Power+BI+Training&iso=20160509T1300&p1=22&ah=3”)),
To this:
Source1 = Web.Page(Web.Contents(WebURL)),
Now I have a dynamic query based on parameters. Let’s change the parameters in Excel and Refresh the data.
When I refresh the data I can see in the status bar that Power Query is refreshing the whole data set based on input parameters (illustrated in screenshot above).
And here is my final result:
Here is the full script of example above:
let
// read Input Data
input = Excel.CurrentWorkbook(){[Name="InputData"]}[Content],
inputrecord=Table.First(input),
date=Text.From(inputrecord[Date]),
time=Text.PadStart(Text.From(inputrecord[Time]),4,"0"),
duration=Text.From(inputrecord[Duration]),
WebURL="http://www.timeanddate.com/worldclock/fixedtime.html?msg=Power+BI+Training&iso="&date&"T"&time&"&p1=22&ah="&duration,
Source = Web.Page(Web.Contents(WebURL)),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Header", type text}, {"Location", type text}, {"Start time", type text}, {"End time", type text}, {"Location2", type text}, {"Start time2", type text}, {"End time2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Header"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","*","",Replacer.ReplaceText,{"Location"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","*","",Replacer.ReplaceText,{"Location2"}),
#"Removed Columns2" = Table.RemoveColumns(#"Replaced Value1",{"Location2", "Start time2", "End time2"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value1",{"Location", "Start time", "End time"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Location2", "Location"}, {"Start time2", "Start time"}, {"End time2", "End time"}}),
#"Appended Query" = Table.Combine({#"Removed Columns2", #"Renamed Columns" }),
#"Grouped Rows" = Table.Group(#"Appended Query", {"Start time", "End time"}, {{"Count", each Table.RowCount(_), type number}, {"Cities", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Cities 2", each Table.SelectColumns([Cities],{"Location"})),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Cities 4", each Table.Transpose([Cities 2])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Cities 5", each Table.ToList([Cities 4],
Combiner.CombineTextByDelimiter(", ")
)),
#"Expanded Cities 5" = Table.ExpandListColumn(#"Added Custom3", "Cities 5"),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Cities 5",{"Start time", "End time", "Cities 5"}),
Final = Table.RenameColumns(#"Removed Other Columns",{{"Cities 5", "Cities"}})
in
Final
You have seen how Power Query can be useful for even none BI use case scenarios. You’ve learned how to use Power Query to get input parameters dynamically from Excel spreadsheet and call a web URL with that, fetches the data set, and do data transformations. This was a real world use case that showed you some strengths of Power Query. No matter if you are a BI developer or not, you will find use cases of Power Query in many situations.
Tell me real use case scenarios that you have used Power Query for, how did you find Power Query in solving that challenge?
Power Query Not for BI; Event Date and Time Scheduler – Part 1
Power Query Not for BI; Event Date and Time Scheduler – Part 2
Published Date : May 14, 2016
In first part of this series I’ve explained that Power Query can be used for non-BI scenarios, and here is a real-world use case example; Creating an event date and time scheduler which consider multiple time zones. In previous post you’ve learned how to use Power Query to do some data mash up to build a unified list of cities in different time zones and start and end time of event in each city. In this post we will continue steps to build the final result set. If you want to learn more about Power Query read my Power BI online book; Power BI from Rookie to Rock Star.
After creating the full list, I want to group rows by their start and end time. I want to create groups of cities where start time of the event is the same (and definitely end time would be the same).
I start by clicking on Start time and End time columns and then I choose Group By from the Home menu under Transform. In Group By window I can see that Start time and End time already selected as Group By columns (I can add or remove from this window by clicking on – or + buttons if I want to).
I create two new columns for group by result set, one as a count of all rows in each group (this is just out of curiosity to see how many cities I have in each start/end time group), and the other one as all rows for each group (this will generate a table for each group, containing all cities of that group; all cities with same start/end time);
Output of this step will be a table with unique start/end times plus two new columns; count of all rows, and a table embedded column for list of all cities.
I can click on a cell in the Cities column to see contents of it (Do not click on the Table word, click on an empty space in that cell. If you click on the Table word a new subsequent step will be generated to fetch that specific table).
Content of each cell in above screenshot is a table containing all cities in that group. For example, for the selected cell above I have a table of 22 cities.
So now my next step is to create a string concatenated list of all these city names, like: Addis Ababa, Amman, Ankara, Antananarivo, Athens…
I can transform this column to be concatenated list all in one, but for simplicity and ease of understanding I’ll do that step by step by adding a custom column in each step;
As the first step to concatenate, I’ll just fetch single column from the sub-table which is Location .
I create a new custom column by click on the Add Column menu, and then Add Custom Column icon. This will open Add Custom Column window for me where I can define a new column with the name and the expression to calculate the new column.
I name this as Cities 2, and the expression as
= Table.SelectColumns([Cities],{"Location"})
Table.SelectColumns only fetches specific columns of the table that we mention in the {}. Here I only fetched Location column, so it will be a single column table as a result;
For concatenation I want to use a method that needs values to be on COLUMNS not on rows. So I have to transform the inner table to show values in Columns rather than rows. Fortunately it is easy to do with Table.Transpose. I add a new custom column with this expression:
= Table.Transpose([Cities 2])
This simple expression will turn my single-column row values into multiple columns in a single row. For using Transpose All you need is table name. Table should be single columned. And then it transforms it to rows. Column headers would be auto named as Column1, Column2,…. So this would be the result;
You might wonder what is happening when you add a custom column which is based on an expression which uses an existing column. With a glance at Advanced Editor you can see this is the script line generated for adding this custom column;
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Cities 4", each Table.Transpose([Cities 2]))
Table.AddColumn adds a new column with name and expression. This function gets three parameters;
EACH is a singleton function that applies on every row of the main table, and produces the result of expression for that row into the new column.
Now that I have values in multiple columns I can concatenate them all into one string with Table.ToList function which converts a table to List. This function can concatenate all columns of table into one column (because List is a single columned data structure).
The actual concatenation happens by Combiner function; Combiner.CombineTextByDelimiter(“, “) which concatenate values with a delimiter which I set to be comma. So here is the expression for my new custom column:
=Table.ToList([Cities 4],
Combiner.CombineTextByDelimiter(", ")
)
And the result would be a list which contains concatenated string value;
All I have to now is to expand the list to its values. Expanding a column means expanding its underneath structure. Expand column appears when you have a multi value structure in each cell. Multi-value structures in Power Query can be; Table, List, and Record. You can find Expand Column Icon simply besides the column heading and click on it.
After expanding I can see concatenated text values in my main table as below;
Now I have Start/End time for each time zone, and concatenated list of all cities in that time zone, so I just do a bit of polishing with removing extra columns. Only keeping Start Time, End Time, and Cities 5. And renaming Cities 5 to Cities.
I’ve made my result set as I want with Power Query so far, but still one step left, which is using parameters and Automation; I’ll be using an Excel table as input parameters to set my local event date and time, and then Power Query will use that input parameters to refresh the whole result set. Stay tuned for the next post to see how it can be done.
Power Query Not for BI; Event Date and Time Scheduler – Part 1
Power Query Not for BI; Event Date and Time Scheduler – Part 3
Published Date : May 13, 2016
Previously you have read how to use Power Query as the data transformation component of Power BI and you learned how to use it in BI applications. You can read more about Power Query usage in BI in Power BI online book from Rookie to Rock Star. In this series I will explain how to use Power Query for non-BI usage. This data transformation tool can be used anywhere to use input parameters, fetch the data from web or any other data sources, mash up the data to form the required shape, and automate the process. In this series I will show you a real use case of using Power Query for non-BI application.
As an introduction to this series, I want to take you to the path that leads me to use Power Query here. You might be aware that I am teaching Power BI courses, and most of my courses are online and Live. This means that courses are not recorded videos, it is me on the other side of the line with full interactive audio and video experience with students with Go2Meeting application. Students connecting to me from other places in the world. So I do need an event date/time scheduler that I can announce date and time of the event in different time zones.
Fortunately there is a very good website that helps to find a date/time in different time zones. In this website I can set my input parameters as the date/time of my event locally (in my city), and name of the event, and duration.
Then the website produces a result set of important cities in different time zones with the date and time of the event in their time zones. That’s brilliant, isn’t it?
The main issue here is that; I want to use this list in my course announcement web page, and list above is overwhelming! If I copy and paste list above, it will make my page so long. Audience will lose interest to read such a long page to find their city and local time zone. What I need is this:
So as a result I decided to use Power Query, because with Power Query I can;
So Let’s now start building this solution step by step.
By completing this scenario, you will learn a real-world use case of Power Query for non-BI solution. You will learn using Power Query for;
This example has been done with Excel 2016 which has Power Query embedded. So if you have Excel 2016 you don’t need to install anything. If you are using Excel 2013 or 2010 you need to download and install Power Query for Excel Add-in. Power Query editor experience in all of these versions of Excel are the same. The only difference is that Power Query menu options in Excel 2016 is located under Data Tab;
And in Excel 2013 and 2010 are located under Power Query Tab;
I’ll start by the easy part of the work, which is using a static web URL like this:
In Power Query I can fetch tables produced in this web page. Follow the menu as: Go to Data Tab (In excel 2013/2010 Power Query Tab), New Query, From Other Data Sources, and then From Web
Enter the URL mentioned above in the dialog box
Power Query is smart enough to separate each table, and It shows you a preview of each table. Choose the table that has values with cities and date/times.
I can see in preview above that there are some extra columns, and also I need to apply some transformations. So I Click on Edit to lunch query editor for further data manipulation.
When query editor lunches and Power Query loads the preview of table, it also automatically applies some data type changes as the very first step. Here is the data set when it loads in query editor for the first time;
There is a Header column which is not giving me any useful information, So I’ll remove it by right click on the column and Remove column
There are also some * characters in cities names (at the end of some cities, such as Barcelona*). So I remove them by clicking on the Location Column, then choosing Replace Values from icons under Home menu, and then in replace values dialog box I replace * with blank.
I’ll do the same step again Location2 Column this time. Here is what my result set looks like so far;
As you can see in screenshot above all applied steps so far are listed in the right pane (numbered 3 in screenshot above). This is one of the great features of Power Query that allows you rollback changes anytime you want and check preview of query for specific step.
In screenshot above I have to set of columns; first one numbered 1, and the second one numbered 2. These are not repetitive values. You can check city names in columns Location and Location2. The fact is that timeanddate.com website split the result set into two set of columns and produced HTML table like that. So for reading the full data set, I have to separate these two set of columns and then combine their values into a single set of columns.
So I’ll produce a result set for the second set of columns with removing the first set;
This will give me the second set of columns as below;
Then I rename the column names for this data set. The reason for this rename is that later on when I want to combine two queries together column names should be the same. I’ll rename Columns to be Location, Start Time, End Time. for rename I just right click on each column and Rename
And here is my result set for the second data set:
Now I want to produce another variable for the first set of columns. Instead of creating the whole process again from Get data from web, I start from one step ago which was before removing columns. As I’ve mentioned earlier I can grab that status easily from the Applied Steps section in Query Settings pane. There is a step named Replaced Value1. This step belongs to where I replaced * characters with blank for Location2 columns.
I Click on Replaced Value1 step and then choose second set of columns to remove.
When I click on Remove columns as above, I see a message says; Are you sure you want to insert a step? I get this message because this is not the latest step in my query editor. As long as I know what I am doing adding an extra step is fine.
I hit Insert for the message above and a new step will be created called Removed Columns2. With the result set as below;
When I insert a step in Power Query, the step after that will source from inserted step. This will cause some issues in my scenario. I inserted an step and removed Location2, Start time2, and End time2. and then in Removed Columns1 I want to remove other columns and in Renamed Columns rename columns that does not exists anymore. So I have to fix the reference issue happened by inserting the step. Here is how I fix it:
I go to View tab, Advanced Editor (or I can find Advanced Editor button in Home tab). This will open Advanced Editor with M code for me. Then I replace the highlighted part to be referenced from Replaced Value1.
Now my data sets exists as below:
Removed Columns2: the data set for first set of columns
Renamed Columns: the data set for second set of columns
Now It’s the time to combine these two data sets. There is a menu option that combines two data sets. However, it only appends two QUERIES. As I have everything here in one query (but different steps or let’s say variables), so I can’t use that option. Fortunately, I can do that with a single line of M scripting.
I click on Advanced Editor to see the M code generated behind the scene. Then I Insert code line below at the end of LET clause, after adding a single comma (which is line separator in Power Query) in the line before it.
#"Appended Query" = Table.Combine({#"Removed Columns2", #"Renamed Columns" })
Statement above creates a new data set as a variable called Appended Query. I’ve used Table.Combine to append queries (or UNION them in SQL and Database terminologies). This function is a simple function and gets data sets to be combined which are Removed Columns1 and Renamed Columns1.
Then I show this step in the result set with writing that in IN section:
in
#"Appended Query"
Now my result set have both data sets combined into one. (I can check this includes 144 columns for 144 cities; combined list of 72 cities in each of previous data sets).
It will be really long if I mention all steps in a single post, So stay tuned. Next step would be Grouping rows and applying some transformations to get the desired output. Finally I will show you how to use parameters in the query and automate the process.
Power Query Not for BI; Event Date and Time Scheduler – Part 2
Power Query Not for BI; Event Date and Time Scheduler – Part 3
Published Date : April 12, 2016
In every reporting solution you might feel KPI as a requirement. KPI or Key Performance Indicator is a measure for business to understand how they are acting in specific area that is very import for their business. Power BI supports built-in visualization for KPI, and there are also some good custom visuals that can be used for this purpose. In this post you will learn how to create KPI visualizations with Power BI.
Key Performance Indicator is a measure for business to understand how they perform in specific area that is important and many times critical for their business. For example they would like to see how the current year to date sales is going against what they estimated? Is it higher or lower? What is the trend? Is it going upward or downward? There are some visualization options to show KPIs, and from them the most commons are; Gauges, Charts with arrows that shows upward or downward, and color code of red, amber, or green (visualizing the status).
Fortunately Power BI has a built-in KPI visualization (released 29th of Feb 2016) which can be used for this purpose. There are also some great custom visualizations that I found really useful and like to share them with you here.
KPIs measuring something and show trend and status. So they have components to work with as below:
There might be other components as well, but let’s keep it simple. and work on these elements. In below example you’ll see how we apply attributes and configurations for each visualization element to visualize KPI there.
For running examples of this post you need to install AdventureWorksDW like other examples of Power BI online book.
As I mentioned earlier KPI recently added to Power BI and you can find it in built-in visualization list in visualization pane.
Using this item is easy as it only has three data fields to configure:
I’ve only used FactInternetSales and DimDate from AdventureWorksDW in this demo. For simplicity I got the target value as static value. I’ve created a measure in FactInternetSales called Target, and assigned the static value 1,000,000$.
As I said this is just for simplicity. Your target value in real world scenarios might come from a forecast table, or a budget table or something like that.
In the report area, create a slicer on Calendar Year. and then drag and drop a KPI element into the report. set Indicator as SalesAmount, Target goals to be the Target measure, and Trend Axis to be EnglishMonthName (Before doing that you have to Sort EnglishMonthName column by MonthNumberOfYear in the Data Tab). Then this is what you get:
As you see the KPI now shows me the value or indicator (which is Sales Amount) in the middle with large font ($1.73M), and the Goal or target under that with smaller font ($1M), and the percentage of the value compared with goal besides it (+73.18%). You also see this colored green which means status is good or in the other words value is higher than expected. in the back ground you also see an area chart of value over period of months (Green area chart shows value over months). You can change the calendar year in slicer and see how the KPI change.
Now lets see another KPI and how to change Status on that. For the other KPI I want to see TotalProductCost as Indicator, and everything else exactly the same. For Costs usually the lower is better which is different from previous KPI. In the first KPI the higher value means the better result, the higher sales is desired. But for Costs I expect decrease. So I can simply change the Status in the Formatting area of KPI to have the direction as Low is good. and here would be the result;
Now you see that despite the fact that TotalProductCost is greater than the goal still it shows it as red, because low is good, and high is bad. The default behavior for Status is that High is good.
Bullet chart is a custom visualization published by Microsoft. You need to download it from Visuals Gallery and then import it into Power BI. This chart has many fields to configure, you can configure them all or part of them. Required fields are Value, Target Value. You need to also set Minimum and Maximum to appropriate values. There are also areas with red, amber, and green colors that can be assigned with Needs Improvement, Satisfactory, Good, and Very Good fields. For this chart I’ve used a sample table with single line as below:
And I configured Bullet chart with fields from this table combined with FactInternetSales, and DimDate as below;
As you can see in the above chart which is for calendar year 2007; that for some months like December the sales amount is in good range (from $1M to $2M). for some months it is in amber range like August and June (between $500K and $1M), and for some it is below $500K and are in red range such as January.
As you see in above configuration I haven’t used Satisfactory and Good range. Have a play with this chart and configure it to see what you get out of it.
Another custom visualization which gives us the gauge visualization is DialGauge. This visual has been published by CloudFonts Technologies LLP. like the normal gauge for KPI this component gives you flexibility to define ranges for green, amber, and red. The Pointer Value is the value we want to evaluate. You can also set a percentage for this gauge. I’ve used this gauge with the sample table I’ve mentioned earlier in this post. and SalesAmount as the pointer value. For this chart I’ve used another slicer for the month in addition to the slicer for the year.
As you see the pointer value stays in green area because sales amount of October 2007 is $1.08M which is between $1M and $2M. You can check what you see if you change the month to August (expect pointer to fall into amber area).
LinearGauge is the custom visual published by MAQ Software. You can set minimum and maximum values, target value, and two trend values. As an example in below chart I’ve used SalesAmount as the value, and other information from the sample table above.
As you see the target value showed with a vertical line in $1M point. the orange filled section shows the value which is sales amount $1.08M for month October 2007. Percentage against target is 108.04% which is +8.04%. Trend values shows that sales amount is going upwards compared to each trend value.
In this post you’ve seen some visualization items that are useful to show KPIs. There will be definitely more charts and custom visuals in upcoming months. However this post gave you a better feeling about how to use them, or what items to look for and configure when you deal with KPIs. If you are interested to learn more about Power BI, please continue reading Power BI online book.
Published Date : April 10, 2016
Power BI free allows you to have 1GB data per user, and pro 10GB. However in many organizations you have much bigger size of data than 10GB, sometimes you deal with Tera bytes, even Peta bytes of data. Is Power BI limiting you for the visualization? Short answer; No! In this post I’ll explain you options that you have when you deal with large data sets. As any other solutions there are pros and cons of working with large data sets with Power BI, Let’s explore them together.
First of all I have to mention that Power BI compress each data set effectively before loading into memory, this is one of the big advantages of xVelocity In-memory engine that Power BI, Power Pivot, and SSAS Tabular built on top of that. Compression happens automatically, that means you don’t need to set a configuration, or allow Power BI to do something. Any data set will be compressed in a reasonable level when you import the data into Power BI.
As an example I’ve imported a CSV file with ~800 MB data size. When it loaded into Power BI I had only 8MB power BI file to work with. That’s an impressive compression.
However compression rate isn’t always like that, it depends on type of data set, data type of columns and some other facts. The bottom line in this section is that, if you have a data set that is more than 10GB and won’t grow significantly, then consider loading that into Power BI to check how compressed it would be, maybe you get it under 10GB limitation and then you are good to go 
If that is not the case then continue reading.
With Power BI we can connect live to some data sources; On premises or in cloud. Well, here’s the trick; if your data set is large, then use Live connection! simple but useful trick. You can have a SQL Server, Oracle or whatever data source you want with any size of data you need, and create a live connection to it with Power BI. Live connection won’t import data into the model in Power BI. Live connection brings the metadata and data structure into Power BI, and then you can visualize data based on that. with every visualization a query will be sent to the data source and brings the response.
Let’s look at an example of such Live connection to SQL Server on premises database. I have a database with a large table contains 48 million records of data. the table itself is ~800 MB
I know it is not more than 10GB, but the method would be the same even if I have 10TB sized data table. this table is big enough to show how live connection works for this example.
When you go through Get Data and select this table (or any other tables in the data set) and click Load, you’ll see the option to choose between Live connection or Import. DirectQuery means Live Connection.
After creating the live Connection you can create relationship in the model, or build visualization for that.
There are however some limitations with Live connection which I’ll explore later in this post.
** For the Live connection to on-premises data sources you need to have Power BI Enterprise or Personal Gateway installed and configured.
You can connect to wide range of on-premises data sources, such as other databases, and also SSAS. as an example you can do:
There are also many Azure data sources that you can use for the live connection Azure SQL Database which is the similar database engine of SQL Server on-premises (with some differences of course) but on cloud. Azure SQL Data Warehouse is the cloud database structure which supports both unstructured and structured data. Azure SQL DW is capable of expanding the compute engine of the database regardless of the storage engine of that. And Sparks on Azure HDInsight is the other azure data source that can be used for live connection to big data structure on azure.
Live connection is good to connect with large data sets, and won’t load the data into the model, so Power BI solution would be very small. However there are some limitations for this type of connection, such as; No DAX support, no data model formatting in the Data tab of Power BI, No multiple data sources support, and No Power Q&A support. Let’s go through them one by one;
With Live connection there won’t be any Data tab in Power BI to create calculated measures, columns or tables. You have to create all calculations in the data source level. It means that if you have SQL Server as the source, create all calculated columns or measures in SQL Server source tables. Your modelling should be done through the data source. In Power BI desktop you would be able to only set up relationship and that’s all in terms of modelling in Power BI.
Formatting is not also available through the Modeling tab of Power BI, So if you want to set decimal points, or set a data type of column it is not possible through Power BI. You need to handle all of these through the data source (or Power Query, read further down).
Fortunately visualization part of Power BI is fully supported in Live connection mode. The underlying reason is that visualization is a separate engine in Power BI, and this is one of awesome reasons that a product built on top of separate components usually works better than a product all in one with no underlying component. You can build any visualization and you won’t be limited at all.
With Power BI one of the great advantages is that you can combine data sets from multiple sources, multiple data bases or files can be participated in building a model. Unfortunately when you work with Live connection you won’t be able to have data from more than one data source. Even if you want to get data from another database on the same server you will face messages like below;
So adding another data source requires data to be imported, which is not useful for the scenario of working with large data sets.
As you know one of the features of Power BI website is natural language questions and answering engine called Power Q&A. This feature is not available (at the time of writing this post) in Power BI Live Connection. when you have a live connection your dashboard won’t have Power Q&A question box on the top.
Fortunately Power Query still is available with Live Connection. This gives you ability to join tables, flatten them if you require, apply data transformation and prepare the data as you want. Power Query can also set the data types in a way that be more familiar for the Power BI model to understand. If you want to learn more about Power Query read Power Query sections of Power BI online book.
In screenshot above you can see that I’ve joined DimProduct, DimProductSubCategory, and DimProductCategory to bring all fields in single table; DimProduct. And this all happened with Live Connection.
Live connection to data source means report will send queries to data source. Data Sources are different in terms of response time. SSAS tabular might produce faster result, and normal SQL Server database slower. Don’t forget that all performance and indexing tips should be considered carefully when you work with Live connection. If you are working for example with SQL Server consider proper indexing, column-store indexes and many other optimization and performance tuning tips.
Just for a very small example of performance tuning; here is what performance I get when I have normal index on my table with 48 Million records;
A regular select sum from my table with 48 million records takes 4 minutes and 4 seconds to run. and the same query responds in less than a second when I have clustered column store index;
and significantly improved performance when I have a Clustered Column Store index on the same table with same amount of data rows;
I’m not going to teach you all performance tuning in this post, and I can’t do it, because you have to read books, blog posts, watch videos to learn that. That is a whole different topic on its own. and the most important thing is that; Performance tuning is different for each data source. performance tuning for Oracle, SQL Server, and SSAS are totally different. Your friend for this part would be Google, and vast amount of free content available on the internet for you to study.
Published Date : April 7, 2016
Turning columns to rows, or rows to columns is easy with Power Query and Power BI. There are many situations that you get a name, value data source, and wants to convert that into columns with values underneath. On the other hand many times you get multiple columns and want to change it to name, value structure with a column for name, another column for value. That’s why Pivot and Unpivot is for. In this post I’ll get you through basic pivot and unpivot. If you want to read more about Power Query read it from Power BI online book.
Consider we have a data source like this:
above data set is budget information. If we want to spread the table with a column for every month, we can simply use Pivot as below:
first click on the column that contains names, in this example it would be Month column. Then from Transform menu tab, choose Pivot Column.
The Pivot dialog box asks you to choose the Value column, which is Budget Amount in this example
and then simply and easily I have the pivoted result set;
You can see that I have a column for each month now. Year column was just passed through. I can have as many as columns I want to pass through. The important factor for Pivot is that there should be a name column, and a value column. You can also see the Table.Pivot script of Power Query generated for this example in above screenshot.
Now let’s see what happens if name value is a bit different;
In this example I have two records for a single name (Mth 11 2010 is repeated). and for some names I don’t have any records at all (Mth 4 2011 is missing)
Pivot dialog has the option to choose the aggregation function, and that is specially for cases that a name appeared more than once in the data set. default aggregation is Sum,
So the default Pivot will result as below:
However if the aggregation be set to Do Not Aggregate. then you will get an error when a name is repeated in the data set
Here is the error value in the result set;
and the error would be:
Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
List
So Pivot is easy and simple to do, but you have to be careful about the nature and quality of source data set. If it is normal to have a name repeated in the source data, then an aggregation needs to be set properly. if you expect each name to appear once, then setting it as Do Not Aggregate works better because you can use error handling mechanism in Power Query to handle error somehow.
Unpivot does the reverse. it turns multiple column headers into a single column but in rows. and store their values in another column. Here is an example of Budget data that usually you get from finance department;
You can click on Columns that you want to unpivot, and then select Unpivot columns (or you can do reverse, select pass through columns, and select unpivot other columns);
and then unpivoted result set would be as below:
As you see columns and their values are now converted to rows split in only two columns: attribute, and value.
If you get a repetitive column in the source data like below;
Then you would get that repeated in the attribute field after unpivot;
So the best way to handle that is to identify the repetitive column before applying unpviot. You can do these types of checking with Power Query scripts and other functions, If you want to read more about Power Query read it from Power BI online book.
Published Date : April 6, 2016
You can create relationships in Power BI between tables. Relationships are useful for some functions to work across multiple tables and produce the result. Relationship between tables also makes visualization and report elements more efficient, because result of selection in one chart can affect another chart from different table. However there is a limitation in Power BI relationship that you can’t create relationship based on more than one column. In other words if you want to create relationship (or join two tables) with two or more columns, you cannot! Fortunately there is a work around that I’ll explain in this post. For this post you need to be familiar with Power BI and Power Query, if you are not, read them through Power BI online book.
Assume that we have a budget table with fiscal year, fiscal period, and budget amount. Here is an screenshot of this table:
If I want to do date analysis and date based calculations it is best to create a relationship between the budget table and a date dimension. Here is a view of my date dimension: (Here is an example of creating date dimension with Power Query, and the script for creating date dimension in SQL Server)
To join these two tables I can add a day column to the budget table, and then join them based on three columns: fiscal year, fiscal period, and day (day of month). So here is the budget table with the new day column added and month value a bit polished to remove “Mth” from the month label;
Now if I want to create relationship between date dimension and budget table based on these three columns I cannot! The create relationship dialog doesn’t allow me to select multiple columns, and because with a single column a Key field won’t be identified so the relationship can’t be created.
The workaround for this problem is easy. Power BI doesn’t allow relationship in model based on multiple columns, but Power Query can join tables with as many as columns we want. So what I can do as a workaround is to join budget table to date dimension in Power Query and fetch the date key. then I’ll use the date key as a single field relationship in Power BI modelling section.
First I open Merge Queries from the Combine section of Home tab;
Here is how I join two tables based on multiple columns: I can hold CTRL key and select columns one by one (in the right order of joining)
Then I’ll have the new table embedded as a result of the join;
So I’ll just pick the key field from embedded table;
And after save and closing query editor window I can create relationship in Power BI model based on a single column;
Published Date : April 5, 2016
If you be familiar with Power Pivot you know that there is a functionality in Power Pivot for setting a table as a Date Table. This enables time intelligence functions such as year to date to work properly over your data model. However this option is not available in Power BI Desktop. There is no way that you mark a table as a date table. So time intelligence functions might not work properly in Power BI. In this post I’ll show you some methods that you can get time intelligence functions working.
Prerequisite
For running samples of this post you need to have AdventureWorksDW database installed on your SQL Server. If you don’t have it download it from here and restore it on your machine.
If you are not Familiar with Time Intelligence functions, or DAX, or even Power BI; read Power BI online book‘s other chapters.
Let’s start with calculating year to date in Power Pivot. So I’ll start by creating an Excel file and going to Power Pivot tab of it, then Manage
In Power Pivot editor I choose to get data from external data sources, and from SQL Server.
And I choose two tables from AdventureWorksDW database; DimDate, and FactInternetSales.
I then make sure that the connection between tables is based on OrderDateKey (active relationship);
Now lets create year to date measure in FactInternetSales with simple TotalYTD Dax function as below:
Sales YTD:=TOTALYTD(SUM(FactInternetSales[SalesAmount]),DimDate[FullDateAlternateKey])
I also order months with the correct order of their number as below
Now I can analyze this model in Excel PivotTable;
When I look at Sales year to date value I see that it is not working properly, and it only shows same Sales Amount as YTD value. which is not correct obviously
Reason for this behavior is that TotalYTD is a DAX time intelligence function and for that to work correctly I have to mark a table as a Date Table in my model. There are some requirements for the table to be marked as date table;
fortunately DimDate in our example meets all criteria above, so I’ll go to model and under DimDate, under the Design tab, I choose Mark as Date Table;
There will be a dialog box asking for the full date column in your table, which in our case is FullDateAlternateKey (the column with date data type)
After this change now I can see that Sales YTD shows me the correct value (sales amounts adds up for each year):
So as you see it is easy to get time intelligence functions in Power Pivot working by only marking a table as a date table. Things aren’t that easy in Power BI unfortunately.
There is not an option for marking a table as date table in Power BI (at least at the time of writing this post). So if I create same model in Power BI with same relationship, and same measure I’ll see it won’t work correctly
Everything for building above model is same as what I’ve explained in Power Pivot. the only difference is that I’ve used Matrix from Power BI visualization to show report above.
I’ve found two ways to fix the issue so far;
I can all an ALL(<date table name>) filter to the time intelligence function to get it working right. Here is the correct version of year to date calculation in Power BI;
Sales YTD = TOTALYTD(SUM(FactInternetSales[SalesAmount]),DimDate[FullDateAlternateKey], ALL(DimDate))
Two important points here:
So now as a result I have correct year to date:
Let’s test it with another function:
SamePeriodLastYear is a DAX function that returns the same period (date,month, quarter) from the last year. I want to use it to calculate last year’s sale. If I do it without ALL I see this
Last Year Sales Incorrect = CALCULATE(SUM(FactInternetSales[SalesAmount]),SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))
This shows me nothing!
Now if I add ALL(DimDate) in the filter area of SamePeriodLastYear as below:
Last Year Sales = CALCULATE(SUM(FactInternetSales[SalesAmount]),SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]),ALL(DimDate))
And the result would be correct:
Even If I do more complex time intelligence functions such as Rolling 12 month (more information on Alberto’s post here), I can add ALL(DimDate) to get it working correctly:
Sales Rolling 12 Months = CALCULATE( SUM(FactInternetSales[SalesAmount]), DATESBETWEEN (DimDate[FullDateAlternateKey], NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(DimDate[FullDateAlternateKey]))), LASTDATE(DimDate[FullDateAlternateKey]) ), ALL(DimDate) )
I haven’t checked ALL(<date table>) with all time intelligence functions in Power BI to see if it works for all of them or not, but I believe it would work for most as most of them accept filter. However if you found somewhere that it doesn’t work, try the second method as below;
I saw this first on Ginger Grant’s blog post, so all credit goes to her. this method is simple, but works perfectly! The big warning for using this method is that if you want to apply this on an existing model you might need to re-create your model, because Power BI after the Import will re-write the solution. So let’s see how it works;
You’ve seen how I’ve created an Excel file with Power Pivot and the DimDate marked as date table earlier in this post. What you need to do is to do same but only for your date dimension;
Create an empty Excel file with a Power Pivot model with only single table: your date dimension. Mark this table as Date Table in Power Pivot, and then save the Excel file somewhere.
In Power BI import the excel file: Note that Import is different from Get Data. To Import; go to File menu, then Import, and then choose Excel Workbook Contents.
Address the Excel file here, and the DimDate table from Power Pivot will be loaded into the model;
Now you can add all other tables you want and write time intelligence functions normally. Don’t worry about anything because you have the date table configured.
For my example I add FactInternetSales, and create the relationship between that table and DimDate, and write YTD calculation as a measure with this script:
Sales YTD = TOTALYTD(SUM(FactInternetSales[SalesAmount]),DimDate[FullDateAlternateKey])
And here is the result correctly as expected:
As I’ve mentioned earlier the limitation (or difficulty) with this method is that you have to do your date table first and then rest of the model afterwards. If you have an existing model in Power BI then with Import your model have to be re-created which is a pain in most of the cases.
I’ll update this post again if I found other methods.
Published Date : April 1, 2016
Calculated tables first introduced in September 2015 update of Power BI Desktop. The name speak for itself; these are tables created by calculation. As these are in-memory tables their calculation is based on DAX (Data Analysis eXpression language). There are many benefits of using Calculated tables, such as using them for role playing dimensions (for example having more than one date dimension in a model), There are some DAX functions and expressions that returns a table as a result, and using them as table in your model sometimes is really helpful. for example you might want to create a table for top 10 customers and then use that as the main source table in many reports. In this post I’ll explain to you some use cases of calculated tables.
For running examples of this post you need to install Adventure Works DW database on SQL Server, or you can download Excel version of it from here:
The very first functionality that appears in mind when we talk about Calculated Tables is ability to create role play dimensions. Role Play dimensions are dimensions with the same structure and data rows that plays different roles in our data model. For example Date Dimension is a generic dimension. However in a sales transaction table you might have more than one date columns to relate with the date dimension. in example below we have three date fields in FactInternetSales table: Order Date, Ship Date, and Due Date.
There three fields should be related to three different date dimensions (because tabular model which Power BI is based on that, doesn’t support role playing dimensions built-in). So what you can do is to just load the date dimension once in Get Data section from the SQL Server (Here is the T-SQL script for creating date dimension), or by a blank query from Power Query script (Here is the Power Query script for creating the date dimension). Here is the example date dimension loaded in Power BI Desktop (through Get Data):
Now I can create role playing dimensions with creating a Calculated table:
and this will create a table in memory for me and allows me to write definition of the table
The language for table definition is DAX (Data Analysis eXpression). If you want to know more about DAX you have to read DAX section of the Power BI book. For now let’s keep it simple to see how it works in action. We want an exact copy of the DimDate table here. so I can simply use ALL function in DAX as below:
Ship Date = ALL(DimDate)
As soon as I type the expression above and press Enter I’ll see the result underneath it as data rows, and also list of columns in the Fields pane. I’ve created my role dimension as simple as that. Now I can set up the relationship;
For the relationship above I’ve created also a Due Date dimension, and renamed the original DimDate to Order Date.
Calculated table loads into memory, so your Power BI file size will increase. However you don’t need to read them again from the external data source. Yes, you can create multiple views in the source database and connect to them through Get Data section with Power Query, however their data need to populate from the source database every time a refresh happens (either scheduled or manual).
WITHOUT Calculated Tables: Here is example of three date tables loaded from the external data source:
WITH Calculated Tables: and here is only one date dimension loaded (for the role playing dimension example above):
As you can see this is much more efficient in terms of reducing the refresh time. However the memory consumption would be the same in both methods.
Date dimension was a narrow data set example. You might need role playing for big data tables, so Calculated tables will save you a lot of time in refreshing data in such case.
There are some DAX functions that return a table. For example ALL function which I used in the role playing sample above. ALL was a simple example of a DAX function that returns the whole copy of the source table. Let’s have a look at some other examples and see how it works in other scenarios.
There are many examples that a business considers top 10 or top 20 customers and filter down whole dashboard and set of reports only for them. Usually the main reason is that top 10, 20 customers will bring majority of revenue to the business. Fortunately there is a TOPN function in DAX which helps us to build such calculation. TOPN function returns a table. With TOPN we can choose how many rows we want in the result set, and the grouping function to be applied (if there is any) and the aggregation (if there is any).
In this example I want to show you how to use calculated table to generate a list of top 100 customers. As a business requirement I want to visualize the total revenue from top 100 customers and compare it with total revenue of the whole business. There might be different ways to calculate and visualize it, but I want to do it with calculated table as a sample scenario.
Summarize is a DAX function that generates a grouped by list from a table. Summarize works similar to Group By in T-SQL. So if I want to create a table with CustomerKeys and their total sales amount, I can write this expression:
Customer Sales = SUMMARIZE(FactInternetSales,FactInternetSales[CustomerKey], "Total Sales", SUM(FactInternetSales[Total Sales]))
Here are details about parameters I passed in the expression above to Summarize function:
So as a result I will have a table with CustomerKey and Total Sales.
Now that we have list of customers with their total sales, it is easy to get top 100 customers. I can simply use TOPN function like this to create another calculated table (I could do this example with only one calculated table instead of two, but I only did it with two table to help you understand the logic better);
Top 10 Customers = TOPN(100,'Customer Sales','Customer Sales'[Total Sales],DESC)
And expression above means:
and here is the result:
I also renamed the Total Sales column to Top Customer Sales (as you see in screenshot above).
Now I can simply build a report in Power BI to show the difference between Total Sales and Top Customer Sales (If you don’t know how to create visualization below follow the visualization chapters of Power BI book):
Great, We’ve used calculated tables for getting some insight out of top 100 customers and compared it with the total. There are many other cases that you can use Calculated Table for. Chris Webb mentioned using Calendar function in his blog post as a calculated table to start building a date dimension.
As any other DAX related limitations, calculated tables very first limitation is memory. You need to have enough memory to use these tables. This limitation is also an advantage on the other hand, because in-memory structure makes these calculations really fast.
The other limitation which I like to mention at this stage is: Not Inheriting Formatting.
By not inheriting formatting I mean the calculated table doesn’t inherit format from the source table. In some complex scenarios where the calculation comes from many tables that might not be necessary. but for our role playing simple example above; If my original date dimension has some formatting configuration. such as setting DateKey to a “Do Not Summarize”, or some other configuration, then I would like to see the same in the calculated table fetched out of this.
The formatting applied on the calculated table columns also will be overwritten after each change in the DAX expression.
Download the Power BI file of demo from here:
Published Date : March 23, 2016
Power BI utilizes two built-in map charts; Map and Filled Map. Filled Map is a map that shows data points as Geo-spatial areas rather than points on map. Areas can be continent, country, region, state, city, or county. Working with Filled Map however isn’t as easy and convenient as the map chart is. You need to set the data category of fields in the data model appropriately to get the Filled Map working correctly. There are also some limitations in filled map with countries that address hierarchy is different from State, City, County. In this post I’ll get you through some tips of using filled map in your Power BI solution. I also show you some existing issues in the current version of filled map and things that you need to be aware of when you work with this visualization element.
As I mentioned earlier working with filled map requires a bit more steps than the regular map. Let’s go through that with a real example; This Wikipedia page includes list of all regions in New Zealand with their population. So let’s create a filled map for this data to show areas with their population.
I can use Get Data -> From Web and use the URL of the Wikipedia page to explore the data set. I can remove some extra columns and rows in Query Editor window easily (If you don’t know how to work with Query Editor read the Power Query introduction part of this Power BI series). I also add a custom column called Full Region with the country name attached to the end of region name (because there might be another Auckland in another country which is not what I want to show in my map). So here is what I have at my query at the end:
Now if I directly use Region Full in a Filled Map, I’ll see an error message that says; More location data is required to create a filled map. Click to learn more.
Details: To create a filled map, the location data should include Bing Map-supported geographic data, such as country/region, state/province, or postal code.
Configuring a data field as a geographic field is easy; All I need to do is to go to Data Tab. click on my query, and then on my data field. Then from the Modeling tab under the Properties pane set Data Category to State or Province.
After the change when I get back to the Filled map I can see that it illustrates regions on the map nicely.
As you see in the Data Category options you can set the field to be Continent, Country/Region, State or Province, City, County, Post Code…. If you have the geographic values in your data set then you can easily map them to these data categories and filled map will draw them nicely for you.
I’m living in New Zealand (if you don’t know know it already ;)) and the geographic structure here is different from State, City, County. What we have is more like Country, Region, District, Suburb. for example: Country= New Zealand, Region= Auckland, District =Franklin, Suburb= Pukekohe. You’ve seen in example above that I used State or Province data category for Region and it worked fine. So I thought I can map District to the City data category as well, but Here is what I get when I map cities in Auckland to a City data category:
However when I change data category to County I get it working (not perfect though! there are some white spots in the map!);
and Here is the category I choose for it:
So as you see I have to play with to map my data fields to a data category which generates a result close to what it is. And as you see I don’t get a clean result. I see an area (Papakura) in the map above which is white (no data points) which should be drawn as part of one of Districts (Manukau City). I did a search on Google Map and Bing Map to see if they can show me boundaries of districts, and found out that they can’t. Here is what Google Map shows me:
And here is what the actual boundaries should be (from Wikipedia)
The orange area in map above is much bigger than the area mentioned in Google Map. So no wonder why Power BI shows some areas blank. So I can’t blame Power BI, because it seems to be related to the boundaries definition in Bing maps which is not up-to-date. However at the end of the day this won’t give me the result I’m looking for.
Believe it or not, there are some situations that you can’t find any appropriate data categories to map to your geographic data field. This might not happen in USA geographic information, but I found it when I tried to go one level down to the Suburb level. The sad fact is that I couldn’t find a data category that maps to suburbs in some areas in New Zealand! Here is what I’ve done;
I’ve mapped suburbs to County because that was the lowest level I’ve found in data category for geographic information. (Place and Address cannot be used for Filled Map at the time or writing this post). and I got Nothing! Not event a small area on the map. I’ve tried then removing the district and putting suburb, region, country format with County as the data category which didn’t helped again.
I’ve found that I can map some locations based on Postal Code as you see below. However not Postal Code is not always good distinguishing field for a region, as multiple regions might have a postal code shared.
Then I tried to find Latitude and Longitude of each suburb in Bing Maps and use that for Filled Map, but I’ve found that Lat and Long fields in Filled Map isn’t working in the way they supposed to work!
There was no other method I could try to map suburbs with Filled Map unfortunately. So the sad end of this experiement is that with the current version of Filled Map I cannot show some suburbs with Filled Map.
Filled Map is good visualization element but it has some limitations. You can use it for areas which their boundaries are clear, such as Country, Region, States. However when geographic structure is different from built-in structure in Data Category types, then you might face some difficulties and won’t be able to generate the visualization you want. I hope Microsoft BI team fix issues related to this graph soon (And I believe they will, because they did a really good job in Power BI extensive improvement through past months). If you want to learn more about Power BI feel free to read rest of Power BI online book from Rookie to Rock Star.
Published Date : March 11, 2016
In previous chapters of Power BI from Rookie to Rock Star you’ve learned that Power BI reports and models can be published in Power BI website for sharing, scheduling and some other purposes. One of the features in Power BI website which is unique to Power BI and is not available in many BI tools in the market is Power Q&A engine. Power Q&A is a natural question and answering engine in Power BI. With Q&A you can ask your questions from the existing model with natural language and get response with visualization elements! In this post I’ll show you some of intelligence questions that you can ask from Q&A to get a very useful response.
If you want to run sample of this post and see how they works in action you need to create the Publication dashboard from the Power BI report built on top of Pubs database. In one of my earlier posts in the book I explained how to create that report. You can also ask similar questions from any other model in your Power BI dashboard with just changing name of fields and values to whatever you have in your dataset.
When you published your Power BI file into the Power BI desktop, usually you create a dashboard for it. For Power Q&A to work (the version of Power Q&A at the time of writing this post) you should create a dashboard for your report. After creating the dashboard you will see the question bar of Q&A on the top of your dashboard.
Let’s start by the most basic question:
by typing name of a dataset you can get it in a table view. You can either type in name of the table; such as “sales” or put a “show” keyword before it likc “show sales”
As you can see the Q&A is also smart enough to suggest you a sentense to ask the question.
You can filter values with a WHERE keyword in your sentense. example below fetch all sales for the book titled “Is Anger Enemey?”
You can do date filters as well simply by mentioning the date. below sample shows sales for year 1994:
You can explore data from related datasets. For example to get titles and authors (which are in two separate datasets) you can ask:
You can use Ascending or Descending commands in your question.
Also notice that you can change the order with simply hovering your mouse on each column and clicking on sort icon that appears there. I also have the “As Table” at the end of question above that force result set to be viewed as a table visualization.
You can filter on equity of values. such as sales on the payerms Net 60
Use “between” keyword to filter for a range
You can mention the exact date simply like sales October 28, 1993
You can filter dateset for dates before or after a date as below
Fetch data with filters such as past or next periods. for example fetch dataset information for past 10 years or so.
You can even do calulations in your question, how impresive is that. For example ask Total Sales amount or Average Sales Amount
You can then aggregate across attribute values. such as; average sales amount per author
You can ask what type of visual you want to see the result set to be shown.
Now that you know some keywords to put in your question, you can ask questions that have combined items to create more useful reports such as:
You can pin every answer you want to your dashboard to save it for later.
There are some other keywords that helps you to ask better questions and get better answers from Power BI, but let’s keep this post simple for now. In future posts I’ll explain how to build you Power BI model in a way that responds best to question from Q&A. tips in creating relationship, naming columns, data types and etc.
Now go to your Power BI dashboard and have fun with Q&A and bring some of examples you’ve done. I’d love to see what you will find