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.

Azure PIM (Privileged Identity Management)

We can analyze the user roles on Azure under two different titles of Azure AD roles and Azure Resources roles as a platform. Microsoft has released the Azure AD Privileged Identity Management product for the management of these roles.

What can we do with the Azure AD Privileged Identity Management PIM product?

-You can assign privileged roles and define duration with the ticket or approval method for Azure AD or Resources.

– You can analyze all the action of the person who assigned or who got assigned by fetching activity logs through Azure AD Privileged Identity Management dashboard.

You need Azure AD Premium P2 or Office 365 E5 license to be able to use this product.

 

For Azure AD roles,

We can move on to the action steps.

-Open the product by writing AD Priviliged Identity Management into the Search field on Portal Azure.

-A dashboard like the one below welcomes us when we open AD Privileged Management. We can do the necessary authorizations with the shortcuts on this dashboard.

-After clicking Azure AD roles, go to settings and click roles.

-Enable the Ticket in the field how the role will be active when it is assigned to the user.

-Go back to the Azure AD Roles tab and go to Roles. Click Add Member and choose the user to whom you want to assign the role.

-Add the related user. The roles can be assigned as Eligible or Permanent. Since we will assign a role with a duration, choose Eligible.

-We assign the role like you can see below.

-A mail like the one below is send to the related user. Click the activate role button. You can access the related field by going to https://portal.azure.com with the user information and opening AD Privileged Management.

-Choose activate on the page that is opened.

-You can specify a custom activation hour and have the role activated later. Ticket number and Activation reason fields are required fields and their content is optional. Write the content the admin or user have determined into the Ticker number field.

-After the activation action, the role has been activated like you can see below.

-You can see that the role has been activated and the expiration duration on the Azure AD Privileged Management dashboard.

-Another role assigning method is the Require approval method. Let’s activate it in the related role like below. And choose the admin user that will approve.

-Open Manage-Roles.

-Choose Add Member and add the related user.

-Go to https://portal.azure.com with the related user and open AD Privileged Management. Click the Activate button.

-Write content optional to the Activation reason field. Click the activate button.

-The role will not be activated right away. Its status will be updated as pending request. A request will be sent to the Approver user we’ve stated in the role settings.

-Approver requests with the Approver user are seen like below.

-After the request is approved, the role is activated.

-The related user is assigned to the role.

-A mail stating the the role is active will be sent to the user who we’ve assigned the role to.

 

For Azure resources,

-You can assign roles in Azure resources with Azure AD Privileged Identity Management. Open PIM through portal and click Azure resources. Then click Discover resources and add an available subscription.

-Click the subscription you’ve added and go to settings like below. List your Azure resources roles. The steps from now on are the same as the ones with Azure AD roles.

-Go into the owner role and click the edit botton.

-Determine if you will request MFA from the user who we will assign the role to before activating the role. You can take the same Ticket or Require approval actions as the Azure AD roles about how the role will be activated when it is assigned to the user.

-Go back to Azure resource and click roles.

-Click add member and assign the role.

-You can activate with a Ticket or Approval like we did in Azure AD Roles steps. A mail like the one below is sent to the user. Click activate roles and complete the activation.

-We can analyze logs in My audit history for Azure AD Privileged Identity Management Azure AD Roles and Azure Resource.

 

Hope to see you later.

 

Insert Data from Picture in Excel

An amazing feature is waiting for you on your Mobile Excel.

With the Insert data from picture feature that you will use on mobile devices like cell phone or tablet, you can get data to Excel from a picture.

Insert Data from Picture

Click the button on bottom left corner to view the Excel tabs.

You will see an image with a camera on it (Data from picture).

Click the Data from picture button and take a picture of your table.

After you take the picture, your data will be arranged in the background to be transmitted to Excel.

When the actions are complete, your table will be ready.

 

The action is as short and fast as that..

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.