Power BI : 2020 March Favorites

Hello, here we are with the Power BI March update! I realize how fast the months go by with the new updates. This month, we have some long-waited and nice updates. In this sense, Power BI users’ opinions were taken into consideration. If you have all buckled up, let’s start!

 

1- New Action Alert

The most eye-catching feature in the Power BI March updates! New action options are available for the buttons we add for more interaction and a better application image:

  • Page Navigation
  • Drill through

We tried to find our way with the Page Navigation action to this day: by adding a bookmark with the most unfiltered version of the other page. Microsoft found out that we solved it like the and stepped in and offered this as an action option to do this the legal way. I can spend all my applause on this feature.

 

 

page navigation
Page navigation

 

The second one is one of the actions we wanted to see in the scenes. We were so happy about even the “Right-click to use the drill through.” text! -Because normally we had to go to charts and right-click to find that feature!- That’s why we are extremely happy about it.

tablo detayını gör
Activating the Drill through feature through the “See table details” button.

 

When we choose the Drill through as action, we can print the chosen one with conditional formatting and this helps a lot in terms of having an image that puts people into action. Also, you can write tooltip texts  for when its active/passive.

Koşullu Biçimlendirme&Enabled Tooltip
You can print the selected value by using conditional formatting for the text in the button and make it visible by writing any text you want to the Enabled Tooltip area.

 

Disabled Tooltip
You can write what you want to do into the tooltip field in order to activate the button.

2-Multi-column sort for tables

Feels like it was just yesterday that you were crying “But why? We need this feature!” and I was saying “I know, I know, it is so ridiculous but it is what it is!” And now we finally got this update! To add more columns to the sort order, Shift + click the column header you would like to add next in the sort order.

birden çok sütuna göre sıralama
When you click a column title with the SHIFT key, it accepts that column as sorting measure as well.

3- Dual axis for line chart

It was our subjects in recent trainings! Now we can add a second axis that allows us to draw two trends with different ranges along the same X-axis progression. We can compare two different units in the same graphic at the same time. Like Sales Amount&Sales Total, Sales Tex&Sales Total…

4-Filter Pane Search

It was a feature that was available in web but not in desktop. Now it is available for desktop too. You can search the filter you want in the search area. This features is activates as default. You can turn this feature on or off in the reports settings of the Options dialog.

Filtrelerde arama

5-Updates to decomposition tree

Some people were very happy when the decomposition tree came us default to Power BI! The decomposition tree now supports modifying the maximum bars shown per level. The default is 10 and users can select values between 3-30. Setting a low number is particularly handy if you don’t want the decomposition tree to take up too much space on the canvas. That way it’s more useful.

ayrışma ağacının yeni özelliği

6-New DAX function: COALESCE

A new DAX Function enters our lives with the Power BI March updates. The COALESCE function returns the first expression that does not evaluate to BLANK. So, what the hell does this mean? There are already a lot of functions as “That does not evaluate to BLANK”… They syntax of this function is:

COALESCE(, [, ]…)

 

It goes like that step by step and shows us the non-blank one. As outcome, it returns us a scalar expression. We can use it in an example like that:

When we do a calculated and show in on card, we don’t want the blank expressions to be visible. We want something like 0,1,100,”-“. For this we use the IF(ISBLANK(…) expression. Right at this point we can use this function.

COALESCE (SUM (FactInternetSales [SalesAmount]), 0) = IF(ISBLANK(SUM (FactInternetSales [SalesAmount])),0)

7-Relations get stronger with ArcGIS!

Product of Esri -the most known map and cartography company of the world: ArcGIS. We have been using its maps as default anyways for a long time and knew that more detailed features would come. Most features are available for Premium and some can be used in Pro. First of all, let me show you the location of ArcGIS maps.

ArcGIS
ArcGIS location

When you click here, there are 3 types of connections:

  • ArcGIS Enterprise
  • ArcGIS Online
  • Standart

 

The ArcGIS Enterprise and ArcGIS Online options are for users who have the premium app subscription and provides all the capabilities of Standard and extra capabilities, including additional geocoding, technical support, and access to mapping reference layers, and more. The Standard option is free and provides basic mapping capabilities. I leave the features below for you to check them.

arcgis maps capacity&preferences

Multiple Reference Layer

All premium app users can now add multiple reference layers to a single map visualization within Power BI. A reference layer is information represented on a map. It adds context to your operational business data. For example, let’s say you have mapped your store locations in Power BI. You can now overlay it against reference layers such as income, age, competitor locations or other demographics to gain valuable insights. You can add data and layers that are published and shared online by the ArcGIS community as well as layers from your ArcGIS Online or ArcGIS Enterprise organization.

New table of contents

A new table of contents that will help all ArcGIS Maps for Power BI users (free and premium) better visualize their data on a map has been added. Now, when you drag data to a location field well and see it on a map, you can also see a table of contents that lists all the layers on the map and shows the features represented by the layers. This allows your report viewers to quickly understand the data that they are seeing.

arcgis_map

8-Data Sources

Is an update without a new data source an update at all?

  • HIVE LLAP: This connector provides both Import and Direct Query capabilities and the ability to specify Thrift Transport Protocol as ‘Standard’ or ‘HTTP’.
  • Cognite:  The Cognite Power BI connector enables data consumers to read, analyze, and present data from Cognite Data Fusion (CDF).

We have shared our favorite updates for this month. You can click here for our other articles about Power BI. Click here to download the latest version of Power BI Desktop.

Good game well played.

 

The SEQUENCE Function

In this article I will be talking about the SEQUENCE Function that is one of the dynamic array functions. It allows us to create a list of sequential numbers in an array when we want to give a sequence number in a column or we give a starting number and indicate the increase value. We can easily use it when we need to quickly create a data set. You can find articles about other functions on our blog.

 

WHAT DOES IT DO

The SEQUENCE Function -like I mentioned above- is a function similar to the Flash Fill option in the Fill field in Excel. For example, when we want to give a sequence number from 1 to 20, it is going to be enough for us to write =SEQUENCE(20). Or let’s say that you are going to create a 10-row and 5-column numeral data table. When we want these numbers to start from a certain number and increase by the value we indicate and fill the sequence like that, we can use this function. We can do extra special actions with the numeric values this functions returns us.

 

SYNTAX

=SEQUENCE(rows,[columns],[start],[step])

This function has 4 arguments.
The first one of these arguments is required and the other 3 are optional.

Now let’s take a look at what these arguments mean, i.e. what this function wants from us and what we will give it.

sıralı fonksiyon argümanları listesi

 

WORKING CONDITIONS

  • Any missing optional arguments will default to 1.

  • An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values. In the example above, the array for our SEQUENCE formula is range C1:G4.

  • The SEQUENCE function will return an array, which will spill if it’s the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your supporting data is in an Excel table, then the array will automatically resize as you add or remove data from your array range if you’re using structured references. For more details, see this article on spilled array behavior.

  • Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.

 

USE OF THE FUNCTION

If you want, you can try to give a sequence number in a column. We can obtain a sequence of numbers starting from 1 for the specified number of rows and columns. If you wish, you can increase step by step by using the increment value. Let’s take a look at how to use the function.

sıralı fonksiyonu kullanımı

 

SPLIT THE DATA INTO COLUMNS EASILY

Now let’s have an example of the use in extra special actions…
Let’s assume that there are dozens, hundreds, thousands of row of data in the A column, and you want to split that data into a few columns. Now, we can create the list easily with the numbers it returns with the logic of this function. In the example below, there are 16 rows of data in the A column. Let’s get these written equally in the A and B columns with a row gap.

 

sıralı function farklı kullanımı

 

See you in other articles, bye. 🙋🏻‍♂️
You can share this article with your friends and help them get informed. 👍🏻

coder by

LET : THE NEWEST FUNCTION

This week I will be talking about a function recently announced by Microsoft that only the Office Insider users can use: the LET Function. Like I mentioned in the FILTER function article, as the new dynamic string functions we will be able to execute a lot of actions more simply and quickly. And new features keep coming one after another. You can easily keep up-to-date by following our articles. 👍🏻

 

DEFINITION OF THE LET FUNCTION

In the announcement the definition goes like:

Have you ever had to repeat the same expression multiple times within a formula, created a mega formula or wished that you had a way to reuse portions of your formula for easier consumption? With the addition of the LET function, now you can!

The LET function assigns names to calculation results and makes it easier for you to reuse the parts of the formula. In a way, it means that we can Define Names in a function and use the name in calculation. Sometime when we are writing a formula, we need to state the same range or condition multiple time. the LET function will be helping as right at this point. We will name the value we have to use multiple times in a formula and we’ll indicate it with that name in the function.

The main benefits are:

1. Readability

No more having to remember what a specific range/cell reference referred to, what your calculation was doing or duplicating the same expression within a formula. With the ability to name expressions, you can give meaningful context to readers of your formula.

2. Performance

If you reuse the same expression multiple times in a formula, Excel calculates that expression multiple times. LET allows you to name the expression and refer to it using that name. Any named expression is calculated only once, even if it is referred to many times in the formula. This can significantly improve performance for computationally complex expressions.

 

SYNTAX

LET

(name1, value1, [name2…], [value2…], calculation)

let fonksiyonu

This function consists of 3 basic arguments. Name and value arguments can be optionally increased.

The 3 basic arguments are required. For the function to work, there has to be indicated value and calculation arguments.

Let’s take a look at these arguments.

  • name1: The name for the 1st value
  • value1: The value to associate with the 1st name
  • name2 (optional): Additional names
  • value2 (optional): Additional values
  • calculation: The calculation to perform. This is always the final argument and it can refer to any of the defined names in the LET.

 

Deconstructing the parameters, there are two things to make note of

  1.  The names and their values must be in pairs. If there is a name but there is not a value, the function doesn’t work.
  2. The last parameter of the function is the calculation which can use the values you named. A properly structured LET will have an odd number of arguments.

 

ADDITIONAL REMARKS 

  • The last argument should be a calculation that returns a result.
  • Argument names are limited with the names that can be used in the Name Manager. For example, “a” is valid but “c” will not be a valid name since it coincides with the R1C1 reference styles.

 

This example will help you comprehend it better: 

=LET(total; SUM(A1:A10); total * 3)

Let me give another example so that the software people can comprehend it better: in the formula below, I have created a variable called sum and gave it the 1 value. In the calculation part, I said add +2 to the sum variable and as a result, this function will return the result of 3.

=LET(SUM; 1; SUM+2)

If we want to take this a step further, I mean if we want to give double names and values, we can use it like this:

=LET(total, SUM(A1:A10), count, COUNT(A1:A10), total / count)

Here is another example:

izinver fonksiyon örneği

Here, you need to FILTER the Fred in the first column with the new FILTER dynamic string and if there is a blank cell, you need to enter – (hyphen) icon.

Normally, we can solve this with a formula like this; if there is a blank cell in the result coming from the filter, write hyphen there, if there is no blank cells filter normally. 

=IF(ISBLANK(FILTER(A2:D8;A2:A8="Fred"));"-";FILTER(A2:D8;A2:A8="Fred"))

But if you have noticed, we wrote this part twice in the formula; FILTER(A2:D8;A2:A8=”Fred”)

LET function says that you don’t need to write the same data twice.

=LET(filterCriteria, “Fred”, filteredRange, FILTER(A2:D8,A2:A8=filterCriteria),

IF(ISBLANK(filteredRange),”-“,filteredRange))

 

Let me give another example of this beautiful function and than end my article: 😊

izinver fonksiyonu

The formula we need to write when we want to find the total data of February based on the conditions indicated above in D2 and D3 in the table above: 

=IF(SUMIFS($C$2:$C$8;$A$2:$A$8;$D$2;$B$2:$B$8;">="&$D$3)<0;"Sum Incorrect";SUMIFS($C$2:$C$8;$A$2:$A$8;$D$2;$B$2:$B$8;">="&$D$3))

As you can see, we used SUMIFS two times in IF. We had to write it like that depending on our need.

But if we are to do it with the LET function, we only need to write the SUMIFS function once. 

=LET(Sum;SUMIFS($C$2:$C$8;$A$2:$A$8;$D$2;$B$2:$B$8;">="&$D$3);IF(Sum<0;"Incorrect";Sum))

 

So much for the information I will share with you for now. If you want you can get more information here. As we start using this function in our daily lives, we will be sharing it with you. ⚡️

 

See you in other articles, bye. 🙋🏻‍♂️

You can share this post with your friends and help them get informed as well.👍🏻

CREATE CUSTOM LISTS IN EXCEL

In this article, we will be talking about how to create a Custom Menu depending on your needs in Excel without any codes. You can find articles about other features on our blog.

 

ENJOY YOUR OWN CUSTOM LIST

As you know, there are a lot of menus and tons of features in those menus in Excel, from File Menu to Help menu. Sometimes we spend a lot of time looking for a feature in menus or there are features in menus that we don’t use at all. And sometimes, we are just up for a change. 😃 In all these case, you can create your own custom menu and place it anywhere you want, and just do all actions within that menus. And on top of that, you don’y need any UI Editor to execute this action.

özel menü

 

WHERE TO START?

To create a custom menu, we need to go to the  Customize Ribbon field in the Excel Options. There are two ways to go there.

The 1st way:

  • File
  • Options
  • Customize Ribbon

 

The 2nd way: 

  • Customize Quick Access Toolbar
  • Other Commands
  • Customize Ribbon

 

The window we’ll access through both ways will be this one.

şeridi özellştir

 

Come to this window, choose the Help menu and create a new tab/menu by clicking the New Tab button.

Choose New Tab (Custom), click “Rename” and name the new tab. For example, let’s type in PEAKUP. You can choose “New Group” and name it based on the features you’ll add. For now, we can call it Data Analysis.

Now we can add the features/commands we use the most to this menu.

 

If you want, you can do all the actions we’ve covered so far like in the image below.

özel menü ekle

 

If you right-click the Data Analysis group and choose Hide Command Labels options,

 

komut etiketlerini gizle

Your command will be seen as mini commands in the menu like this.mini etiket

 

You can add multiple groups into a menu by creating New Groups. Or you can create New Menus with the New Tab button and place it in the first place after the File menu.

özel menü yeni

I say create menus like this for a while and just try it out. 😄 You can check here for more details.

 

 

See you in other articles, bye. 🙋🏻‍♂️
You can share this post with your friends and help them get informed as well.👍🏻

The Weekday Function

In this article, I will be talking about on of the Date and Hour functions, the WEEKDAY Function that will helps us in our daily lives. This function returns the number that represents the day of the week of a specific date. It gives us a number between 1 and 7, and so we know which day of the week it is and do actions depending on that. If you want, you can get the details about the Weekday Function on support office. You can find our articles about the other function on our blog. 

 

WHAT DOES THE WEEKDAY FUNCTION DO?

The WEEKDAY Function

gives us a number stating the which day a date is in that week. For example, If we want to know which dates in a column are weekend, we can have them highlighted. Or, doesn’t matter which day the payment days are in the column, if your payment day is Friday you can organize all the dates on Friday in their weeks.

SYNTAX

WEEKDAY(serial_number,[return_type])

The WEEKDAY Function

has 2 arguments.
The first one of them is required and the other one is optional.

Now let’s take a look at the meaning of these arguments, i.e. what this function wants from us and what we will give it.

haftanıngünü fonksiyonu argüman listesi

 

WORKING CONDITIONS

The working conditions of the WEEKDAY function are:

  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

  • If serial_number is out of range for the current date base value, a #NUM! error is returned.

  • If return_type is out of the range specified in the table above, a #NUM! error is returned.

  • If the Calendar is Gregorian, the returned number represents the Gregorian day of the week.
    If the calendar is Hegira, the returned number represents the Hegira day of the week. If the calendar is Hegira Calendar,  the argument number is any number that can represent a date and/or time from 1/1/100 (Gregorian Aug 2, 718) to 4/3/9666 (Gregorian Dec 31, 9999).

 

USING THE WEEKDAY FUNCTION

In the Weekday Function, we choose the date that we want to get the day number for the Serial_number argument first. Then, we choose the type that we want the Return_type argument to return. We will come across the list where the first and last days of the week are stated. For us the option is the one with the number 2, where first day is Monday and the 7th day is Sunday. So, we choose the 2 option for the return_type argument and complete the formula.

 

 HIGHLIGHTING THE WEEKENDS

Let’s assume a list in which there are dates of March in a column. At the first glance, we cannot know which day those dates are. If you choose that column, choose Long Date as Format in the Number group in Home menu, you will get the day name and it might be useful for you. But we don’t want the day name, we want the weekends to be highlighted. We can achieve this with the WEEKDAY Function in the Conditional Formatting feature. Colors are very useful when it comes to analyzing a data.

Let’s start practicing with examples. You can apply these steps with me.
Unfortunately, an available rule that does what we want doesn’t exist in Conditional Formatting.
So, what do we do? We create our own rules with a formula, and achieve highlights based on that rule.
You can find and highlight the weekends like this.

weekday fonksiyon kullanımı

 

SETTING THE PAYMENT DAY AS FRIDAY

Let’s do an example of setting the payment day as Friday.
For this action, we will use the CHOOSE and WEEKDAY functions together.
Try to do this action as well and try to comprehend its logic by repeating few times.

weekday ile ödeme gününü cuma gününe ayarlama

 

See you in other articles, bye. 🙋🏻‍♂️
You can share this post with your friends and get them informed as well. 👍🏻

Edit a PDF File with Word

Editing PDF Format & Word

When you need to edit a PDF file, don’t look for the solution too far away. You can handle a lot of actions with magic features of Office programs. Editing the PDF format feature of Word is one of them. You can work with one product with the Opening PDF feature, without any extra costs.

You can quickly open your PDF files as Word files and start editing right away with the Opening PDFs feature of Word.

Open a PDF File in Word

File tab–> Open –> open the file directly from the location it is stored.

With this action, Word creates a copy of the original PDF, converts it into a Word file and attempts to match layout of the original PDF.

You always have the original PDF file, in case you don’t want to keep the version that Word converts.

Converting from PDF to Word works best with files that are mostly text—for example, business, legal, or scientific documents.

 

PDF Format & Thing to Pay Attention to

But when you open a PDF file in Word, it might not look exactly the way it looked as a PDF.

If the PDF contains mostly charts or other graphics, the whole page might show up as an image. When that happens, the text can’t be edited.

Sometimes, Word doesn’t detect an element, and so the Word version doesn’t match the original PDF file. For example, if Word doesn’t recognize a footnote, it treats the footnote as regular text and might not put it at the bottom of the page.

You can take a look at this page to see the details about opening a PDF file in word.

Application Versions

This feature is used in Office 2013 and all the later versions.

Open your Reports with Report Filter Pages

Before we get started with the topic, I want to talk about PivotTables shortly. When we want to report from tables or lists, we usually start by creating a PivotTable. PivotTables helps us to create tables that we can customize without touching the main data at all. PivotTables enables us to execute actions like reporting, calculating, editing tables easily, formatting and filtering easily. And the topic we’ll talk about in this articles is Report Filter Pages.

In the example below, a PivotTable has been created from a list that containes data like Company Name, Personnel Name Surname. The column titles added to filter field in PivotTable Fields become criteria that can be filtered on the table and we filter with this information. In the sample PivotTable below, the City column has been added to the filters field. And the Personnel Name Surname and Company name columns has been added to the rows field. We will be doing city-based reporting through city-based filtering.

The Long Method

We can observe the change of the table one-by-one for each city chosen from the filter. We might not have some any problems while analyzing a few pages, but when we want to do reporting on more pages we usually don’t prefer this method.

Report Filter Pages

For the City criteria in the filter field, reports of each city will be quickly and practically opened with one click with the Report Filter Pages.

For this action, we click on the Options on the left of the Analyze tab in Ribbon after we add Filter to the PivotTable fields, and choose the Show Report Filter Pages from the list that opens. With this action, the City on the filter field is chosen. If there were a few more filtering criteria, we could choose the criteria based-on which we wanted to report.

Afterwards, the filtered versions of information of cities in all personnel makes sales is opened in different pages quickly.

In the page that’s been opened, we can see the City information. Since there are a lot of cities from A to Z, the cities that didn’t fit in are represented with …

Get practical reports with the Report Filter Pages.

Sputnik Radio “Corona Diaries with Serhat Ayan”

Our COO KadirCan Toprakçı appeared as a guest in the Sputnik Radio Program “Corona Diaries with Serhat Ayan” presented by Serhat Ayan. You can click the button below for the program where we talked about topics like working from home and technological infrastructures.

Manage Formulas by Creating a Formula Screen

You can have an interactive flow or execute visual actions by creating rules with formulas in application screens.

Usually hide, show, freeze, open to edit or change color, location, size actions are done for the determined objects. You can apply all these controls in all application scenarios easily and flexibly.

Depending on the situation in the application scenario, you can connect objects or actions to department, user mail, location, title, marital status or user choice in multiple-choice actions.You can apply all these controls in all application scenarios easily and flexibly.

In this article, we will be talking about managing the conditions in the screen centrally and formula evaluation duration. And most importantly, we will talk about the importance of executing an action after the formula evaluation duration is done.

What is Formula Evaluation?

Formula evaluation is the test done during the result that is created by the written formula. Depending on the performance of the formula, a certain duration is needed.

Why Does Formula Evaluation Takes Too Long?

Depending on the performance of the formula, duration will be longer or shorter. As the conditions in the screen get more complex; main reasons like using nested formulas, waiting for the control of  different objects related to each other to be complete, and executing an action depending on the respond from the data source can make the duration of formula evaluation longer.

For this reason, it is important to make the formula evaluation run as fast as possible. When the respond of a formula that hasn’t been completely evaluated yet is late, the default value can be accepted as a respond and cause wrong actions.

What if a Formula is not Evaluated?

When the respond of a formula that hasn’t been completely evaluated yet is late, the default value can be accepted as a respond and cause wrong actions. Moving on to the next action before the formula evaluation is complete will cause incorrect results. Which means that even the condition you’ve specified is met, since the respond came later it will look like it is not met. When the conditions don’t work as fast as they are supposed to, it is necessary to do actions and review formulas to manage the screens in which formulas run.

How Should the Formulas Be Managed?

  • An evaluation screen should be created for the formulas.
  • Like we always mention, it is very important to pay attention to naming correctly. The Label in which the formula is evaluated should be named based-on what it does and the outcome it creates.
  • The formula should produce 1 and  as a result when it is used with the If function.
  • Since actions like OnSelect, OnVisible,OnScan are instant actions, the formulas that has a long duration should be calculated outside with labels and be run with 1,0 control only.