Power bi and how to organize your measures

 

Power Bi had gain significant traction in the business analytics space. Microsoft had proven to penetrate the market and successfully, introduced the app into a staple in the office productivity as well. In this article, I will not dwell into the history and the features of the tool. Instead, I will show you how to organize your power bi fields for easy and smooth dashboard development. 

Power Bi for dashboard

Yes, it is for that purpose and in the business analytics practice, this is a very useful tool. Seeing the numbers graphically and at a glance had proven efficient in drawing out insight and quick decisions. But, creating those highly interactive charts and graphs, could be harder than thought. 

The development could be messy and to produce that summary or data point could involved layers of calculation.

Navigating in the Development phase

Developing a power bi dashboard could be straightforward ( as what Microsoft intends it to be ). Just a heads up though, you could get lost in the sea of data fields as you get to dive in this application. Most times you will have to need to create customer formulas to draw certain calculation. This customize calculation is what we called as Measures.

Measures

There are actually two types and the are;

• Quick Measure - this is a preset formula most of them are self explanatory such as rolling averages.
• Custom Measure - this is an outright creation of the formula. Pretty much same as having one in a spreadsheet.

How to organize your measures?

Dealing with multiple fields on your data can be confusing. At some point, as you created multiple measures, unknowingly you've duplicated some. So, how do you manage this kind of scenario and somehow create an efficient way of navigating your measures.

Two ways to "5S" your measures

• Folders - this is the most effective approach. Containing your measures into a folder see below for the demo
  
• Simple prefix - this is just a personal labeling approach that I've done which is also effective. Example below shows "#_" as prefix. In this way, I have all measures at the top of the field list. It's helpful for I tend to create more measures when developing PowerBi dashboards. 

Being effective with PowerBi is basically doing 5S on your back-end. The foundation to create useful dashboard starts with sustainable Power Bi desktop (.pbix) files. Making your data fields tidy and easy to navigate is key in the development phase. How about you? Do you have any better approach? Let us know in the comment section below.

Google Apps Script Simple Weather API Integration

Here's a simple approach to weather API integration with Google Apps Script. The objective of this script is fetch weather forecast data from a free API provider Openweathermap. The API provides you the basic weather information from historical, current and even a 30 day forecast. You need to register to this API first you to have access to the free resources. which is just enough to get you running.

Apps Script

In the script, shown the basic steps to build that approach to properly call the API and return the desired weather information you might need in your applications. For a successful run you will have below as the return values to choose from. You can create another function to fetch desired weather information.

The Script:

Google AdWord application

Aside from using it for a weather applications, there's a clever user for this in Google AdWords campaign strategy. Let's say you are run a campaign for a product tied up to a specific season and for that matter, generate's demand for certain weather. 

The key information from above script is the return value weather description(i.e., Sunny, Cloudy or Rainy). AdGroup names could be tailored containing the same weather description for easy script manipulation. You could iterate all your adgroups to detect those names.

There you have it. A quick and simple foundation of this automation. I have a working script that encompass the full AdWord dynamics. For Advanced Google AdWords, this will be a useful approach to optimize your campaigns and protect your budgets. Leave a comment below for your thoughts.

VBA: Here's How to Crawl an Amazon Product Reviews

Web Scraping, a clever approach to extract information from layers and layers of html of a web page. It's a a crude way and an alternative to some developers who don't the right access to an API. I'm sharing to an approach if you need to scrape an Amazon review of a product.

The script that I'm going to share is good only for single product URL scraping. This is to demonstrate how VBA can still be effective for those low tier automation implementation. Basically, applicable to developers still in exploring phase, students and hobbyist just want to have some fun.

I've taken a simple approach and haven't taken time to document. This is a project just came to at instance decided to write this in Excel VBA. Depending on the number reviews in that product, the script will take 2~3 mins to run. Let me know in the comments below your thoughts. 

To run the code, you need to select "GETALLREVIEWS" in your macro's list and you will be prompted with an input box where you can paste the URL of the target product listed in Amazon. The script will return to you a new spreadsheet with the list of the reviews successfully scraped.

The programming language used in this application is VBA(Visual Basic for Application). If you want to learn this coding technique, click Buy Now button below for a book to guide you with your learning.

Here's the script. Have fun

How To: Merge Multiple Workbooks or Sheets in to one

Productivity Tools

Have you ever encountered a file with table split into multiple tabs/sheets? Or, have you ever been in a situation, wherein, you need to merge same table from multiple Workbooks into a single file?

So, how do you merge those multiple sheets? Below are the 3 methods:

• Copy and paste all and append them into a single tab/sheet. That's is manual method. Just don't miss out to select a column or Columns. Or, miss a row or even Rows. This the same approach if you need to merge same tables from multiple workbooks. Tedious, but, what could you have done better.
• Power Query this an advance MS Excel feature made easy with a click of a button. It comes as an AddIn you can install on your spreadsheet. Click LINK to download the app from Microsoft. A complete GUIDE will introduce you Power Query and will post an article about merging spreadsheets with this feature.
• If you are not confident with advance tools and manual, tedious approach. I'm offering a Productivity Tool you can have with your MS Excel spreadsheets. It's a very simple to use and you can have them for only $10. Click Buy Now below to download the app.

After successful download go to LINK for the instructions on how to install this app in your MS Excel. The Feature will be in your Data tab with "Productivity Tools" name. Two features will be available for you to use, Merge Worksheets and Merge Workbooks.

How To: Make a calendar in excel

How to make a calendar in Excel. This could be a weird question for some. But, trust it's still valid. I still hear this question from an average Excel user and been using for years. We are talking of the method wherein we could just lay out full calendar for a month for a year. Or, for a number of years.

So, is there a quick way? The answer is, Yes. There is what you call templates. It's just a two step approach basically:

1. Open MS Excel
2. In here you have two options to get to templates
• When you have just opened Excel fresh, you'll be prompted with the list of the templates. In there, you get to select various templates. Including the Calendar templates
• On a currently opened Excel, go to Files>New then it will bring up you same templates view above

It's that simple! Now, the other question. Does excel have a feature where in, instead of typing a date in a cell. You will select the date from a calendar box? Well, in MS Excel currently distributed. There's none. But, you can download an AddIn for $10. Click below buy button for the AddIn.

After successful download go to LINK for the instructions on how to install on your MS Excel. It the AddIn will be shown in the Insert tab as Date & Time with a calendar icon.

How To: Fix a Lousy Mouse Scroll Wheel

Is your mouse has a scroll wheel on top of it? Are you experiencing some lousy scroll with it lately? Have every wondered what's the life span of the Mouse wheel for its effective function? Now, don't just throw away your mouse, if, it has been such a headache when you try to scroll over a page.

The symptom is intermittent, actually, and I will share to you a clever tweaks or repair that have I done to similar defect. The root cause with such defect actually is a worn out rotary scroll wheel axle socket. The fittings were already “lossy” and the axle cannot grip the socket to drive the rotary switch into motion.

You will need a Precision Screw Driver, and a couple of thin strips of adhesives and a scissor for cutting a tape into strips.

How to: Simple Remedy of a PC that Randomly Crashes

Just because the Post-PC era is near it doesn't mean that there's no point anymore in taking good care of your PC. As long as it is working, keep it and take good care of it. It's not practical anymore in buying a new one. Not of course, if you are shifting to another PC lifestyle. You know, going to the "mainstream".

This would be the most common problem that PC users are encountering recently. It may sound a major problem that only a certified technician can do it. Well, need to worry for here is a simple and do it yourself remedy for such kind of problem.

There are only two major contributors for your PC to randomly crashes. It could be a software problem. Could be due to virus or you have installed an application which cause your OS to Freeze and unable to process anything. And the second contributor could be a hardware problem. So, let's get to work. All you need is yourself, a couple of screwdrivers and this guide of course.