SORT DATA WITH THE SORTBY FUNCTION

THE SORTBY FUNCTION

Hello everybody,

There are functions about sorting among the newly released dynamic array functions. They are:

  • SORT
  • SORTBY

We will be talking about the SORTBY function in this article.

 

WHAT DOES IT DO

With the SORTBY function that is one one the recently release dynamic array functions after Office 365, you can sort your table based on multiple columns/rows and sort order you want in a field without touching your table. Imagine suing the Sort feature we use a lot in Excel through the Custom Sort window. If we want to sort by multiple fields, we choose a column there and use one of the A to Z or Z to A options in the Order field and then choose the other field and choose the sorting order again. Thus, our table is sorted by the columns/rows and order we chose. Now we can do all this with one single function.

 

SYNAX

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…) 

There are 3 arguments in the function.
The first two are required, the others are optional.

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

array

Required

 The array or range to sort
by_array1

Required

 The array or range to sort on
[sort_order1]

Optional

 The order to use for sorting. 1 for ascending, -1 for descending. Default is ascendi

 

USE OF THE FUNCTION

  • the data range to be listed in the array argument.
  • the data range by which the sorting will be done based on which column/row first in the by_array1 argument.
  • the sort order is chosen in the sort_order1 argument. If it is not stated, the A to Z order is accepted.
  • you can continue by choosing a data range first and then sort order in the next optional array and orders.
  • you have to state at least one column/row in the by_array1 argument.

 

Let’s give an example and see what happens when only the optional arguments are chosen.

 

Now, let’s make the sort_order argument -1 and thus sort by Z to A .

 

SORT BY MULTIPLE FIELDS

We have sorted by one column and order so far. Now it is time to sort by multiple fields and orders. 😉 Let’s sort the Marka(Brand) field A to Z and then sort the Fiyat(Price) field Z to A and then change the sorting order of the Fiyat(Price) field and see the result.

 

 

 

SORT FROM LEFT TO RIGHT IN A HORIZONTAL TABLE

If you wish, you can sort the data in the field you’ve stated as an array by the stated array or order from left to right in a horizontal table. The important thing is to state the array to be returned, field to be sorted and the sorting order. The table will be sorted by the criteria you stated, doesn’t matter if it is vertical or horizontal.

 

We will be talking about the other New Dynamic Array functions in our next articles.
And then we will be able to get things done way easier by using these new functions together. LONG LIVE THE NEW DYNAMIC ARRAY FUNCTIONS!

See you in other articles, bye. 🙋🏻‍♂️
You can share this article and help a lot of people get informed as well. 👍🏻

FILTER: NEW GENERATION FUNCTION

THE FILTER FUNCTION

Hello everybody,

In this article, will talk about the abilities of the FILTER function that is one of the New Dynamic Array functions. As you know, new changes have been coming into our lives with the Office 365. We can tell from the new Office apps updates and fixes published in the Office Insider Fast channel that Microsoft cares about developing Excel -its one of the strongest weapons- and that has focused on this product with Teams.

 

I mentioned in the XLOOKUP article that I was going to talk about the FILTER function details in its own article. Now, it is this function’s turn.

 

WHAT DOES IT DO?

Imagine creating Filters in one field or more. You can list all the record quickly without filtering the rest of the data on the screen with the FILTER function. For example: List me all the Murats in the A column. As you know, the VLOOKUP function gives us one result and brings the first data it finds, unfortunately doesn’t bring the others. The FILTER function brings us all the records. Now we can list the data as a whole with this function without using macros of writing array formulas. The use of the function is quite simple, keep reading for the details. 👍🏻

 

SYNTAX

=FILTER(array; include; [if_empty])

There are 3 arguments in the function.
The first 2 are required and the last one is optional.

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

array

Required

 The array, or range to filter
include

Required

 A Boolean array whose height or width is the same as the array
[if_empty]

Optional

 The value to return i

 

USE OF THE FUNCTION

First, let’s list the data based on one criterion.
Let’s list based on FİRMA(COMPANY) and all the records of that company. Let’s choose bring the data of the company we choose from the FİRMA SEÇ(CHOOSE COMPANY) cell and if you like, let’s send a message like “No Record” if no data is found. For this, when you write no record into the [if_empty] argument; the text will be written into the cell if a data will not be returned.

 

USING MULTIPLE DYNAMIC CRITERIA

Into the insert argument of the function, we can list the results as a whole if there is data that match the criteria you’ve stated in multiple fields. For example, let’s have two criteria and list the data that match these criteria in the COMPANY and PRODUCT fields. For this, we write each of our criteria into parenthesis and add * (star/cross) in between the criteria.

 

We will be talking about the other New Dynamic Array functions in our next articles.
And then we will be able to get things done way easier by using these new functions together. I can’t help but wish that these formulas have had come out 15 years ago. 😀 LONG LIVE THE NEW DYNAMIC ARRAY FUNCTIONS!

See you in other articles, bye. 🙋🏻‍♂️
You can share this article and help a lot of people get informed as well. 👍🏻

GET RID OF DUPLICATE RECORDS WITH THE UNIQUE FUNCTION

THE UNIQUE FUNCTION

 

Hello everybody,

In this article I will be talking about one of the The Dynamic Array Functions -the UNIQUE function. Before this function, we had to write a long nested formula and even an array formula in order to list the unique data in one or multiple columns. But now we can list the unique data in the field you want with a single and very simple function.

 

WHAT DOES IT DO

The UNIQUE function allows you to create from the data in the stated field. Use this function to return a list of unique values in a list or range.  When you press Enter, the results can be displayed in a contiguous range with a suitable size.

 

SYNTAX

=UNIQUE(array; [by_col]; [exactly_once])

There are 3 arguments in the function.
The first one is required, the other two are optional.

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

Argument Description
array

Required

 The range or array from which to return unique rows or columns
[by_col]

Optional

 The by_col argument is a logical value indicating how to compare.

TRUE will compare columns against each other and return the unique columns

FALSE (or omitted) will compare rows against each other and return the unique rows
[exactly_once]

Optional

 The exactly_once argument is a logical value that will return rows or columns that occur exactly once in the range or array. This is the database concept of unique.

TRUE will return all distinct rows or columns that occur exactly once from the range or array

FALSE (or omitted) will return all distinct rows or columns from the range or array

USE OF THE FUNCTION

We use this function to list the unique data in one column or multiple columns. For example, if we don’t know a formula to list the data in a row; we had to copy that column to a blank column and choose Remove Duplicates or we could list the Unique records only with the Advanced Filter. After writing this function, it will be enough to state the range from which we want the unique data.

You will see how easy it is one you watch the GIF below.

We have listed the unique data in the range we have stated by entering the first argument. Now let’s see what kind of results it returns with the other arguments.

The second arguments that is optional is the [by_col] argument.
TRUE and FALSE options are offered to us in this argument.
TRUE: Returns the unique columns.
FALSE: Returns the unique rows.

You can choose the FALSE option for the by_col argument if you want to get the unique data in any field.

UNIQUE / DUPLICATE DATA

The last argument [exactly_once] offers use two options concerning the data to be returned.
TRUE: Returns the data that is displayed only once. (Lists the ones that have been written only once, doesn’t take the ones written multiple times into consideration). FALSE: Returns all the different elements (Lists the unique ones in the range we choose).

We can choose the TRUE option if we want the data to be listed to be unique data only, and the FALSE option if we want the different data only.

Here is an example for that.

 

LONG LIVE THE NEW DYNAMIC ARRAY FUNCTIONS!

 

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

You can share this post and help a lot of people get informed as well.👍🏻

February Product Updates

PASSGATE

  • It is possible for the passwords of any user or user group on the Active Directory to be reset by another person (For example: Manager, Shift Supervisor, Agent Owner, Security Manager) via SMS.
  • Integration: IBM AS/400
  • The installation is completed if the company to be integrated with AS/400 shares the name given to the AS/400 system, authorized username, password, IP address and IBM’s “Client Access” software for Windows with PEAKUP. Related fields on the AS/400 can be used for the User Information Service, Password Reset Service and Password Change Service.
  • Restriction: The password reset feature of the users with PassGate could be disabled when necessary from the management panel. With PassGate, you could define a limited or an indefinite restriction on the phone number of the user whom you want to restrict password reset. Now, in addition to the password reset feature, password change and profile update features have become restricted. Moreover, the restriction can be applied not only on the phone number, but also on the e-mail address, username, phone number.
  • It is ensured that the Password Change Service can be used not only in Active Directory, but also on all platforms involved in the process.
  • The configuration to allow/disallow same consecutive characters for password procedures has been added. In case companies specify their demands on the “PassGate-System Requirements & Configurations” form, their systems are configured.
  • In addition to resetting users’ passwords via the administration panel, the system administrator is also able to unlock their locked their unlocked accounts.
  • It has been ensured that password reset and account unlock processes can be initiated with the user name via the Self-Service Web portal.
  • In addition to the validation rules, warning messages can also be displayed on the password change screen. In case companies specify their demands on the “PassGate-System Requirements & Configurations” form, their systems are configured.
  • The configuration to allow/disallow consecutive same characters for password procedures has been added. In case companies specify their demands on the “PassGate-System Requirements & Configurations” form, their systems are configured.
  • It was ensured that the Law of Protection Of Personal Data Information Text prepared by the companies was positioned in an adjustable manner on the PassGate Web interface. In case companies specify their demands on the “PassGate-System Requirements & Configurations” form, their systems are configured.
  • Other than the verification code on the Self-Service Web portal, the option to add a step in which the ID number has been added. We are able to activate it for companies that want it.

VELOCITY

  • It has been provided that the live weather image in the Header can be customized and managed through the management panel. Companies can add live weather status or any image they want for Header via the management panel.
  • Cross currency integration is complete. Any cross rate can be added to the portal via the administration panel. Not only the cross rate, but also gold (XAU), cryptocurrencies can be listed on the portal.
  • The automatic positioning of the “Play” icon on the video image added for widgets that can be loaded with videos has been provided.
  • For Mega Slider, content or video is provided to be displayed in the modal box.
  • The “Bcc” feature has been activated for widget notification e-mails sent. Companies can separately specify “Bcc” and “To” fields in the e-mail to which notification will be forwarded through the management panel.
  • It is ensured that PDF files loaded into the widget can be downloaded automatically or opened in the browser parametrically.
  • It was provided to parametrically determine whether or not the relevant widget content is shared in e-mail notifications directed to users.
  • The city selection has been made automatically according to the location of those entering the portal.

New Widgets:

  • Law of Protection of Personal Data Documents
  • OHS Documents
  • Human Resources Procedures and Forms
  • Numerator Widget: It is a widget that provides the infrastructure of the correspondence number received for a company to use in its correspondence such as purchase proposal made with companies outside the company. The person receiving the number adds this number to the subject of the e-mail. It is used for regulatory compliance and retrospective monitoring.

Give a Magic Touch to Your Office Programs with Add-ins

You customize you Office products like Excel and PowerPoint by adding features that are not standard.

There are a lot of add-ins like map graphs, Power BI extensions, Forms polls, Emoji and Stickers.

You can find the Add-ins in the Insert tab.

You can choose and use useful and interactive add-ins from various stores.

Excel – Bing Maps Add-in

Allows you to show data like Latitude, Longitude, City and Country in your Excel lists with points on Bing Maps.

Excel – People Graph Add-in

How about using symbols instead of using standard Doughnut or Column charts?

With this add-in, you can express your numeral data with symbols.

You can customize the theme, symbol and chart view and use them the way you want.

Excel – Selection to HTML Add-in

What do you say about an assistant that writes the HTML codes for you?

You can turn all the data in your Excel list -including tables and text- into HTML text directly.

All you need to do is to choose the list range that you want to turn and then press the COPY HTML CODE button that is located in the window of the add-in.

HTML codes have been created and copied in the background instantly, now you can paste them anywhere you want and use them.

Excel – Mini Calendar and Date Picker Add-in

You can quickly plan with the dates you choose from the calendar.

You can customize the features like the week number, the first of the day and color.

PowerPoint – Forms

You can respond the electronic forms during the presentation.

You can add and use any form you want to the presentation with the Forms feature.

Word/PowerPoint/Outlook – Emoji Keyboard Add-in

You can use the emojis you use on your phone in your PowerPoint presentations as well.

PowerPoint – To Do List Pro Add-in

Do you need To-Do lists during a presentation?

You can keep your action steps as a To-Do list while preparing a presentation.

PowerPoint – Web Viewer Add-in

Would you like to show the preview of any website you want while presenting.

You can preview the website without having to stop your presentation and open the browser.

PowerPoint – Pixton Comic Characters Add-in

Make your presentations fun!

You can customize the look, clothes and stance of cartoon characters.

You can make your characters more impressive with speech balloons. Now your presentations are more colorful.

Word – To Do Add-in

You can use To-Do to make your work more planned.

Add information like hour and minute to the tasks and then press the Add Task button. You get to check the task duration with the countdown timer.

EXCEL’S HISTORY (EXCEL FROM PAST TO TODAY)

EXCEL’S HISTORY (Excel from Past to Today)

 

Hello everyone,

I received a lot of questions like “Who found Excel, how was it created? It develops all the time, I wonder how the first version was…” in many of the trainings I gave in corporate companies.

As an answer to there questions, I have prepared the history of Excel and wanted to take the old users to a time travel that would make them say: “”Oh! What times…” And it will serve as an informative article for the new users.

Are you ready for the time travel? 👨🏻‍🚀

 

We met the first Excel as VisiCalc in 1978.
In 1978, Harvard Business School students Dan Bricklin Father of the modern Excel  had to take on an analysis task for a case study. He had two alternatives to do that:

  1. He would do it manually.
  2. He would use a simple and unskillful computer program.

 

He thought that he didn’t have a better alternative and chose VisiCalc in the 1978 fall.
He programmed the first study and prepared the prototype of the concept he called VisiCalc. The first version of VisiCalc was a matrix of 4 columns and 20 rows.
It was not close to the electronic tables of our day but VisiCalc was still an accomplishment for that moment.

That was the first electronic table program and was published for Apple II at first.
It had skills like basic arithmetic operations, recalculating instantly and scroll bars.

 

1978 – VISICALC

 

 

1980 – SUPERCALC

 

 

1982 – MULTIPLAN

 

 

1983 – LOTUS 1-2-3

 

 

1985 – MICROSOFT EXCEL 1.0 (MAC) 

 

 

1987 – MICROSOFT EXCEL 2.0 (WINDOWS)

 

 

1990 – MICROSOFT EXCEL 3.0

 

1992 – MICROSOFT EXCEL 4.0

 

1993 – MICROSOFT EXCEL 5.0

 

 

1995 – MICROSOFT EXCEL 7.0 (Excel 95)

 

 

1997 – MICROSOFT EXCEL 8.0 (Excel 97)

1999 – MICROSOFT EXCEL 9.0 (Excel 2000)
2001 – MICROSOFT EXCEL 10.0
2003 – MICROSOFT EXCEL 11.0 (Excel 2003)

 

 

2007 – MICROSOFT EXCEL 12.0 (Excel 2007) 

 

 

2010 – MICROSOFT EXCEL 14.0 (Excel 2010)

2013 – MICROSOFT EXCEL 15.0 (Excel 2013)
2016 – MICROSOFT EXCEL 16.0 (Excel 2016)
2019 – MICROSOFT EXCEL 16.0 (Excel 2019) 

 

 

2019 – MICROSOFT EXCEL 16.0 (Office 365)

 

Now I leave you alone with the 1992 Microsoft Excel introduction..

 

Well, I hope that you enjoyed this few-minute trip to the past.
You can share this post and take a few more people to the past as well. 👍🏻

Good bye.

CREATE A SPEEDOMETER CHART

SPEEDOMETER CHART

 

Hello everyone,

In this article, we will create a Speedometer chart. We will see how to create it simply step by step. Let me tell you this first: Microsoft will add this Speedometer chart to the standard charts for Office 365 users. I will be talking about the simplest version until then. I have stated what to do step by step below, please try to practice those steps with me. After learning how to create this chart, you can take a look at our Mouse Over Dashboard article. 😉

 

Let’s get started. 👍🏻

 

Before we start creating the chart, we have to have suitable data. We can create this chart with a few methods. We will use a combo chart in a data table to execute this action the shortest way possible.

 

⚫️ Let’s have this data starting from A1.

Indicator Index
25 0
35 2
40 100
100

 

⚫️ Choose any cell and click the Insert menu.

⚫️ Click Combo chart in the Charts group and then click Create Custom Combo Chart.

⚫️Choose the Chart Type of the Indicator as Doughnut on the Add Chart window that pops open. And choose the Chart Type of the Index as Pie and mark the Secondary Axis option, and then click OK.

 

⚫️ Click the + icon of the chart added to the page and unmark Chart Title and Legend.

 

⚫️ Right -click on the green area on the chart and then click Format Data Series. Make the Angle of the first slice 270 on the screen that opens up on right and press Enter.

 

⚫️ Go to Shape Fill in the Format menu and choose No Fill.

 

⚫️ Choose the Series “Indicator” option in the Current Selection field in the Format menu.

 

⚫️ Make the angle of the first slice 270 on the screen that pops open on right and then press Enter.

 

⚫️ We will remove the fill color of the purple piece at the bottom. For this press CTRL + ► (Right Key) 4 times and choose the purple Data Point and then go to Shape Fill in the Format menu and choose No Fill.

 

⚫️Now press CTRL + ► (Right Key) and choose the colors you want in the Shape Fill in the format menu for data points. If you want a bright image, you can use the Shape Effects. Note: If you click the same shape effect twice, it will become brighter.

 

⚫️ Choose the index only, right-click and add Data Labels. And then choose the data label and go to the formula bar, write equals to (=) and choose the B2 cell and press Enter. You can increase the font size of the data label and make it bold. The chart will look like this at this point.

 

⚫️If you want, you can add a Spin Button (Form Control) to the page that would make index move.

On the Developer tab, in the Controls group, click Insert, and then choose Form Controls and draw somewhere suitable in the page, for example to a blank area or a blank cell. Note: You can make the index bolder by increasing the number 2 that is indicated as the Index thickness in the cell B3.

⚫️ Right-click the form control item you’ve added and click Format Control. Go to Cell Link and choose the cell B2. And arrange the others values as the ones below.

 

⚫️Your Speedometer chart will be ready when you choose a circle among the Shapes and add into the middle of the chart.

 

Lastly, I want to share the file I have prepared with you.
You can download the file here. 👉🏻 

You can share this article and help many other people get informed as well.

Goodbye. 🙋🏻‍♂️

Page Redirect Actions

The Navigate function is used to switch between screens in PowerApps mobile applications.

How do you switch between pages?

By touching/clicking the items on the screen (with the user trigger)
Or automatically after a certain period of time (with a timer).

Usually the OnSelect property is used for all the actions that wanted to be executed the moment we “click”.

As an example, let’s take a look at how we switch to the next page when we click the next button on the page.

The page redirection action for the next button:

Navigate (target page, transition effect) will be written into the OnSelect property of this item.

You of course don’t have to write the Navigate formula to OnSelect all the time. We mentioned that different items have different properties. In this sense, properties like OnCheck, OnVisible, OnChange are used when necessary as well.

The OnCheck property can be used for a check box if you want it to work “the moment it is marked”.

POWER BI : FEBRUARY 2020 OUR FAVORITES

Hello dear reader!
February is the same old February again. We survived another valentine’s day. Our lives have gone back to its normal routine. As you know, new Power BI updates come out during these times. And we have come together to talk about this in our article. Seeing some of the features we were waiting made us smile. 🙂

Hierarchical Slicer

They have been asking for this for such a long time! Now, when we add a slicer and put other values under it, it turns itself into a hierarchical slicer automatically. There were filters created for this only back then. The biggest problem of adding a hierarchical slicer is the space it holds up. At first it seem like a small space but then it gets bigger and bigger. And people -naturally- want a report where they don’t have to slide right or left and where they can see the hierarchy at the bottom. I don’t know if they will solve this space problem and if yes how, but I can say that it doesn’t look great when you design the report.

Incremental Refresh

Incremental Refresh allows you to get the latest updated rows from the data source when updating data that is big and that doesn’t get updated everyday and to execute actions faster. This was Premium only before, but there were so many request and messages that they just brought it to Pro.

Incremental Refresh
Incremental Refresh Window

Updates to the new ribbon

Power BI’s ribbon was renewed and previewed in the previous months. Now there are new updates for this ribbon:

  • Now the title of the file we create is in the middle. And on the right, there is user photo and information that we are familiar with from the Office.
  • Now we have Power BI on Keytips. When you press Win + Alt keys, you will access the key tips and freely navigate through tabs. We usually don’t let go of the mouse while creating a report, but this still could be useful.
    tuş ipuçları & başlık çubuğu

NEW FUNCTION ALERT

The DAX family welcomed the FIRSTNONBLANKVALUE and LASTNONBLANKVALUE functions. We has very similar functions: FIRSTNONBLANK and LASTNONBLANK. They syntax is same for now. So, what is the difference between these functions?

For example, let’s use these two functions with the same syntax in a table with Products and their prices.

  1.  LASTNONBLANKVALUE(Table[Product],SUM(Table[Price]))
  2. LASTNONBLANK(Table[Product],SUM(Table[Price]))

While LASTNONBLANKVALUE gives me the second part of the syntax -i.e. the sum-, LASTNONBLANK gives me the first part of the syntax -i.e. the information of which product-.

Data Connectivity

Connectors for the data sources below now come to Power BI as default:

  • MicroStrategy : The MicroStrategy Dataset Connector for Power BI allows users to fetch data from reports or cubes that reside in MicroStrategy project and import them into Power BI Desktop.
  • FHIR : Fast Healthcare Interoperability Resources is the next generation standards framework for healthcare data. .
  • FactSet : FactSet provides integrated software and data solutions for investment professionals
  • TIBCO : TIBCO’s data virtualization capabilities provide you the agility to adapt to new data sources and technology at breakneck speed and cost-effectiveness.
  • Jamf Pro : Using the Jamf Pro instances API, we collect computer and mobile devices details, installed applications, extension attributes, and their group relationships.
  • Asana : With the Asana connector for Power BI, customers can build custom, actionable dashboards based on Asana inputs such as custom fields.

 

We have shared the most important ones of this month’s updated and we will keep sharing. Here is a link for you to take a look at the other articles about Power BI. And here is the link to download this version of Power BI. Take care.