We are thrilled to announce the launch of UrBizEdge’s Personalized for me, this special pack allows you to pick the topics/areas you want to master in Excel and Power BI to achieve your objectives and aspirations. You will also have a trainer take you through applying these to your reports. Our goal is to help you further develop your skills through a personalized learning experience.

With over 7 years of experience, UrBizEdge thus has a unique perspective on the evolution of jobs, industries, organizations, and skills over time. Today we are expanding our current programs by delivering a personalized, comprehensive, course designed just to fit your evolving business needs. Another key advantage of this special pack is having both consulting and training at the cost of just training. 

How to Get Started?

If interested, take a look through these topics listed below and let us know which ones you want to set up a customized one-on-one class around. 

Based on your selection we will then have the class structured into an appropriate number of days. The price for this premium offering is N50,000/day. For most people, 1 or 2 days cover their unique focus areas. We have had huge success delivering this model for many managers and super busy executives who would rather just have a class structured around their needs and usage scenarios with a very personalized touch.

Modern Excel:

• New Formulas in Excel
  • XLOOKUP which is the replacement for VLOOKUP, HLOOKUP, and INDEX + MATCH combo
  • UNIQUE to remove duplicates and create dynamic dashboards/reports
  • FILTER to have a dynamic filter and create dependent dropdowns
  • SEQUENCE
  • SORT, SORTBY
  • RANDARRAY
  • LAMBDA
• Power Query – the ultimate data transformation and cleaning tool in Excel
  • Combining data/reports from multiple sheets super easily
  • Combining data/reports from multiple files super easily
  • Pulling data from multiple sources and cleaning/transforming them
  • Unpivot (a magical data transformation tool)
  • Unrivaled column splitting
    • Split to rows
    • Separate numbers from texts
    • Split by case change
    • Split on every x number of characters
  • AI Insights
    • Text Analytics
    • Vision
    • Azure Machine Learning
  • Groupby (SQL type action)
  • Append Queries
  • Merge Queries
    • Standard Merge
    • Fuzzy Match to handle inconsistent spellings
• Power Pivot
  • Handling data of more than 1.05 million rows
  • Data Modeling (creating relationships between datasets and more)
  • DAX formulas
  • Integrating with Pivot Table
• Deep Dive into Pivot Table and Pivot Charts
  • Using Pivot Table to create very interactive dashboards
  • Slicers and Timelines
• Data visualization and Charts
  • Understanding all the new 18 charts in Microsoft Excel
  • Combo chart
  • Sparklines
  • Conditional formatting as a visual
  • Icons and smart arts (infographics)
• Formulas Deep Dive
  • IF, AND, OR, IFS, SWITCH, XOR
  • SUMIFS, COUNTIFS with wildcards (especially * and ?)
  • ISNA, ISERROR, ISBLANK, ISLOGICAL, ISFORMULA, ISREF, ISTEXT, ISNUMBER
  • VLOOKUP, HLOOKUP, INDEX + MATCH combo
  • EDATE, EOMONTH, TODAY, NOW, DATE, TEXT, YEAR, MONTH, DAY, DATEDIF, NETWORKDAYS, 
  • CONCATENATE, CONCAT, TEXTJOIN, FIND, SEARCH, MID, LEFT, RIGHT
  • SUMPRODUCT to replace VLOOKUP, SUMIFS etc (a must know for anyone claiming Excel expert level)
  • INDEX (one of the most versatile formulas in Excel)
• Creating Dashboards in Excel
• Planning and Strategy Tools in Excel
  • Goal Seek
  • Scenario Manager
  • Data Table
  • Solver
• Integrating Excel with Data Science tools
  • R (using BERT)
  • Python (using xlwings)
  • JavaScript Office APIs
• Power Automate in Excel
• Excel VBA
  • Recording macro
  • Coding from Scratch
    • Userforms
    • Modules
    • User-Defined Functions  VBA

Power BI: 

• Mastering Power Query in Power BI
  • Power Query handbook with M codes
  • Advanced Editor use
  • Column profiling
  • Diving into the deep end of Merge and Append
  • Magic of Right Click
  • Mastering the Power Query interface
    • Enabling the Formula bar
    • Query Settings
    • Query Dependencies
• DAX deep dive
  • Must know DAX formulas (CALCULATE, FILTER, SUMMARIZE etc)
  • DAX Studio
  • Debugging incorrectly working DAX formulas
  • Optimising your DAX
• Data Modeling
  • Relationships
  • Cardinality
  • Direction
  • Synonyms
  • Date Table
• Visuals and Report Creation
  • Native visuals
  • Imported/marketplace visuals
  • Symbols and Unicode icons
  • Report layouting
  • Row-level security
• Report Publishing, Dashboards, and Power BI Service
• AI in Power BI
• Power BI Settings

In summary,

1. You get to choose the topics you want to master.
2. You get to choose the date to have the class.
3. You get more options of venue (our place or your place).
4. You get to have the trainer work on your report for you during the training.
5. You get to improve on the new areas of data analysis you are weak at without being forced to go through areas not important to you as is often the case in general training classes.
6. The best part is that we are setting the fees at a very affordable rate that’s the same as the general classes while you get a personalized training.

Don’t delay. Take advantage of this offer before the price is raised. Reply to us now or call Hope on 0703-990-2445 or Faith on 0913-137-0701.

We recently came up with this innovative solution for busy professionals. It is an industry first: DaaS. Data Analysis As A Service. A subscription based service. In fact, I'll share with you the very announcement letter/email I sent out to our customers/leads.

Make us your secret weapon!

Have you ever experienced the analyst's block (kinda like writer's block, when you just don't know how to make the report you want)? Have you ever felt your report could be better? Have you ever felt like you were not making the most of the data at your disposal? Like there is more you could be uncovering and tracking and reporting? Have you ever felt you need experts you can call on any day and at any time but won't cost you an arm and leg? Okay, maybe I lied about the not costing you an arm and leg. The good thing is that we have structured it flexibly so you can always get more value than the cost. We have created an industry first: DaaS (Data Analysis as a Service). Maybe you have tried training. Perhaps you have attended our in-depth high value training class. You have our video learning materials, our book and our practice files. We hope you remember everything we taught you. However, things are changing. You are changing. Your work is changing. The industry is changing. Excel is changing. Power BI is changing. Everything is changing. You already have a demanding job and can't be spending all day keeping abreast of what's new and applicable to you as regards making the most of your data and reports. We are offering to be your secret weapon. So what's the DaaS structured as? It is simple and straightforward as ABC. A. You can reach us remotely (calls, Skype, emails, SMS etc) and at our office (70b Olorunlogbon street, after Banex hotel, Anthony Village, Lagos) for targeted help with your office work. Note: we won't come to your office or house. B. There is fair usage policy (FUP). So you have to book not less than 24hrs prior and you are entitled to 24hrs/month (which is equivalent to 3 full days) consulting time (both remote and physical). Any length of time above that will attract some extra small charge/fee. C. You have a dedicated resource (more like technical account manager). It is not Michael. He/she is your primary contact, handles all your work requests, communication, meetings and projects. There is a pool of internal resources (Michael inclusive) he/she will draw on, as appropriate, to satisfy all your relevant needs. We have full-time data analysts that are already handling consulting projects and training, so just as skilled as Michael and they are the ones designated as technical account managers. So how much does this cost? That's the best part: there are four flexi plans. N45,000/month. You can cancel anytime and restart anytime (no refunds, when you cancel it means you just don't pay when your subscription elapses). N90,000/quarter N150,000/half-year N200,000/year And it's not negotiable. Why that funny pricing structure? That is the result of our own in-house data analytics on the pricing. We already bill N100,000/day for one-on-one consulting (corporate clients rate is N250,000/day) and even when we are overpowered by the superior negotiating tactics of some of you, we only go as low as N70,000/day for individuals. And that is 8 hrs at a go which is never as effective as two separate 4 hrs, and way less than the 24 hrs spreadable over an entire month that we are offering you at N45,000/month and even less than N19,000/month when you take the year option. We can only service 10 people at the same time. So if you need our service but delay too much, you will have to wait for an open slot if all 10 slots are taken. And just as we can't review the price downwards, so also will we not accept you doubling the fee so we can take you up. What do you think? If interested, get in touch as soon as you can and we will get in touch with you. Reach Michael on 0700ANALYTICS, 0808-938-2423, 0806-312-5227 and mike@urbizedge.com or Hannah on 0802-118-0874 and hannah@urbizedge.com or Emmanuel on 0908-482-5064 and emmanuel@urbizedge.com Again, welcome to the world of DaaS! To your Excel-ling! Michael Olafusi 0700ANALYTICS www.urbizedge.com P.S. We have now fully launched our open class Financial Modelling and Financial Planning course, you can read up on the details and registration steps at https://www.urbizedge.com/FinancialModelling We've spent three years perfecting the curriculum via doing on-request financial modelling training for companies, and building models for both foreign and local clients.

I am working on a whitepaper for the Data Analysis Industry in Nigeria 2017. We did one last year and you can download the report via https://urbizedge.blob.core.windows.net/urbizedge/Data%20Analysis%20Industry%20Report%202016%20-%20Nigeria.pdf or http://bit.ly/2khcyGD

The questions are very interesting and eye opening, also just a couple of mostly yes/no type questions.

You can fill the survey for this year's report via https://freemansh2000.typeform.com/to/eTwjsK and you'll get a free copy of the final whitepaper.

The business world is now data driven and every business professional must now be fluent in the language of data. William E. Deming had the most accurate way of portraying this new age: “In God we trust; all others must bring data.” Without data skills you will have a tough time influencing in the business world. You must learn to manipulate data, make compelling data stories and leverage data for insights.

UrBizEdge Limited, Nigeria’s leading business data analysis company is putting together this special training for proactive business professionals. This training is aimed at making you extremely good in Microsoft Excel, dashboard making, data presentation and business data analysis; teaching you with live business scenarios from our experience consulting for multinationals within and outside Nigeria. It's intended for Sales Managers, Financial Analysts, Business Analysts, Data Analysts, MIS Analysts, HR Executives and power Excel users.

You will get our high value materials, tea break + lunch, our branded DVD with over 20 training videos and practice files, training notepad with pen, a comprehensive training reference material, a training certificate from us (UrBizEdge Limited, a registered Microsoft Partner), after training support and free refresher classes.

The training will be facilitated by a Microsoft recognized Excel Expert with Microsoft Office Specialist Excel 2013  certification and the only Microsoft Excel Most Valuable Professional (MVP) in Africa (there are just about 125 in the whole world and it is the highest level of recognition from Microsoft to an industry expert). We have had participants of our training from Citi Bank, Dalberg, SaveTheChildren, Mobil, Chevron, Vodacom, Nestle, Guinness Nigeria, Nigerian Breweries, Delta Afrik, LATC Marine, Broll, Habanera (JTI), SABMiller, IBM, Airtel, Diamond Bank, ECOWAS, Biofem Pharmaceuticals, Ministry of Finance, FMDQ, Schlumberger, Palladium Group, Nokia Siemens Networks and DDB.

To register reach Michael on 08089382423 and mike@urbizedge.com or Hannah on 08021180874 and hannah@urbizedge.com  or Opeyemi on 09020043560 and opeyemi@urbizedge.com to register. There is a class size limit.

Lagos Date: Friday  25th August 2017 to Saturday  26th August 2017

Lagos Venue: Kristina Jade Learning Center, 70b Olorunlogbon street, after Banex Hotel, Anthony Village, Lagos. 

Port Harcourt Date: Friday 22nd Sepetember 2017 to Saturday 23rd September 2017 for Port Harcourt.

Port Harcourt Venue: Aldgate Hotel, 20B King Perekule street, GRA Phase 2, Port Harcourt, Rivers state.

Abuja Date: Friday 27th October 2017 to Saturday 28th October 2017 for Abuja.

Abuja Venue: Hotel Rosebud, 33 Port Harcourt crescent, off Gimbiya street, Garki 11, Abuja.

The training outline is:

1) Data Manipulation in Excel 

We’ll show you, from a consultant expertise level, how to manipulate data in Excel. From data preparation/cleaning to data formatting the professional way. We’ll cover both the science and the art of data manipulation in Excel, and share very useful keyboard shortcuts and expert tricks that will speed up your productivity in Excel.

2) Data Visualization and Presentation in Excel

A picture is worth a thousand words. And in the business world, it is often the only way to not bore your audience and pass the valuable message you’ve uncovered in your data analysis. We will teach you the foundations of data visualization – from the different types of charts to when to use each of them. Then we will work through business samples to learn the art part of doing data visualization right. You will learn the rules of business data reporting via charts and gain from our industry wealth of consulting for businesses in this vital area.

3) Large Data Analysis: Pivot Table, Pivot Chart and PowerPivot
You should never say you know Excel if you don’t know how to use Pivot Table. It is that important. It is Excel’s premium tool for analysing large data – sales data, inventory data, HR data, transaction data and most business operations data. We are going to cover from the basics to the very advanced use of Pivot Tables. We will show you how to create dynamic reports with Pivot Table; how to overcome some of its layout issues; how to turn off the distracting controls in your final report; how to create calculated fields; how to create Pivot Charts; and the special tricks only a full-time Excel consultant can show you that will turbo-charge you Pivot Table skills. Then we’ll show you how we analyse data of up to (and even above) 30 million rows in Excel. Yes, in Excel. Heard of PowerPivot?

4) Business Data Analysis 

In this section, we teach you the secrets that separate the analysis experts from the people with head/academic knowledge Excel. It is one thing to know the different tools in Excel and it is another completely different thing to know how to expertly mix them together to creatively deliver value at high speed. As full-time Excel chef, we will show you the secret ingredients that make companies consult us even when they have Excel super users in their organization.

5) Executive Dashboards and Reporting
This is the level self-knowledge will not get you to. How do you create an uncrowded insightful visualization for a report with 1000s of rows? Then how about for your sales analysis report of many products and regions? How do you show the different interactions in your data? In short, how do you bring your data to life and take it from a boring confusing mass of text to an interactive exciting visualization? You have to come to get the answers.

6) Excel to PowerPoint

Management level reports are best presented in PowerPoint. When you’ve got a delicate story to tell, you have to guide your audience through a one idea/insight a slide PowerPoint. We won’t teach you how to design slides but we will teach you how to make your chart slides speak very loud. You will also learn the tricks of linking your PowerPoint charts to Excel. It will help cut down the hours you spend on weekly/monthly PowerPoint reports. We will also show you how to embed Excel files in your PowerPoint slide. No more sending separate Excel files when you can embed them right on the very slide you reference their data.

7) Excel VBA
Forget about all you’ve heard about Macros or VBA. Let’s show you how easy and exciting it is to break into the Excel VBA world.

Reach Michael on 08089382423 and mike@urbizedge.com or Hannah on 08021180874 and hannah@urbizedge.com or Opeyemi on 09020043560 and opeyemi@urbizedge.com to register. There is a class size limit.

For a taste of our high quality content, view our free tutorial videos at www.urbizedge.com/tutorials

Today, I finally joined the AWS train. As part of the big data analytics project I am working on for a client, I would need to host a cloud server to mine data off Twitter and Google, saving them daily in a CSV file. Ordinarily, I would have used my laptop as the server for this task, but since this is a commercial project and I can pass the cost to the client, I have to use a dependable affordable virtual server.

I then went on Google to search. I didn't want to go with Microsoft Azure as I have issues with the payment system and I heard Azure isn't the best pocket-friendly option for a very small business. I stumbled on Alibaba Cloud Service but before long I settled for Amazon Web Services.

Setting up was really easy; easier than I expected since it was common statement online that AWS is not as straightforward to use as Azure. Now having experienced both, I disagree.

I used the EC2 service and created a t2-micro Windows Server 2012 R2 instance.

On connecting to the Server via Remote Desktop Connection, I set it up for my data analysis work. I installed Microsoft R Open and RStudio for my R based data analytics work. And I installed Anaconda and PyCharm for my Python based analytics work.

Below is how my server setup looks like.

I even pinned my commonly used tools to the taskbar -- Powershell, Task Scheduler, RStudio and PyCharm.

What is left is how to estimate how much this would cost me per month and if I would need to automate startup/shutdown of the server to save compute time/costs. Some aspect of the startup/shutdown decision will depend on if Twitter approves my request to get unrestricted access to their Tweet database and don't charge me some crazy amount. I have already initiated the request and they have requested I provide them some information they will use to decide whether to grant my request or not. My current way around the limitation in how many tweets per 15 minutes I can scrape is to run the scrapping script every three hours, and still I think I don't get enough representative tweets and some tweets keep showing repeatedly.

Overall, I am loving my new adventure into the deep side of data analytics. And I will always keep you all updated on my progress and share my learning.

The Power BI Desktop is the main tool you would be using in creating Power BI reports. You can freely download it here from Microsoft. 

Once you are done installing it. You get a startup screen like the one below.

There are two major parts of Power BI Desktop you will need to get very familiar with:

1. The Designer part.

2. The Query Editor part.

Let's start first with the Designer part. It is the window you are presented with upon launching Power BI. It has four main sections.

1. The menu section comprising File (for Open, Save, Options/Preference settings etc.), Home, View and Modelling.
2. The Report, Data and Relationship section
3. Page section (like Sheets in Excel), and
4. The context based section that shows Fields and Visualization when you are in Reports, Fields only when you are in Data and nothing when you are in Relationship.

Now to the Query Editor. It is the exact equivalent of PowerQuery (now merged into Get & Transform Data in Excel 2016). Its main function is to help you wrangle data before they are fully loaded/downloaded into the Power BI. So instead of downloading a 16 GB database table and then specifying which fields/rows to keep and which to discard, you can do the specifying using just a preview of the data and only import just the very data you want/need. This is a life and time saver. And space/memory saver too. Then you can do some very interesting and complex stuff you can't do from the Designer part -- like merge or append data from different sources, unpivot and a few other things I find myself doing repeatedly on client/commercial projects.

You get to the Query Editor from the Home menu in the Designer part.

And it has four sections too.

1. The menu section
2. The Queries section
3. The Data section, and
4. The Query Settings section (which only shows up when you have/selected a Query)

And that is it for this second tutorial post in the new beginner to expert series I am doing on Power BI. Cheers!

This month is turning out a very good and busy one for me. I am fully engaged on lots of projects, many are training and a couple are data science projects.

There is one in particular that is going to be the biggest data analysis project I have ever taken up. It will involve daily warehousing of data across Twitter and Google. I would have to probably buy the unlimited Twitter data search access (called firehose). Already contacted the section of twitter in charge of that, Gnip.

Unfortunately, I can't share the details of the project due to its commercial nature. But I should be learning a lot new things that I can safely share with you all.

I am also getting better at using R and Python for very complex analyses. I managed to fix one big issue I have been facing for over six weeks now with my R script scheduling. Turned out that it was relative file path issue that was messing everything up. And I was already thinking it had something to do with R since I had done similar scheduled scripts with Python without any problem. It was a very small problem that has cost me a lot of lost value (days the script didn't work as intended).

These are the types of issues you'll never find in books or from tutorial videos. And it is the main thing I keep telling people wanting to learn data analysis. The only genuine way to learn it is by doing. 

Culled from: https://data4change.workable.com/j/39DA82ABB7
Apply at: https://data4change.workable.com/jobs/493863/candidates/new

DATA4CHAN.GE workshop is a 5-day event at Design Hub, in Kampala. They're looking for incredible, talented people, who are passionate about making a change.

Are you a graphic designer, web developer, journalist, storyteller, researcher, data analyst, or data visualization expert? Then they might be looking for you!

The application process is very competitive, so be sure to put your best foot forward and spend time on your applications, so that they can really understand who you are. DATA4CHAN.GE brings talented people in the visual storytelling community together with human rights organisations that have fascinating original datasets and powerful stories to tell.

During the workshops, interdisciplinary teams consisting of data researchers, coders, UX designers, graphic designers and human rights organisations create data visualisations and devise innovative advocacy strategies. DATA4CHAN.GE is a workshop where HROs and the creative sector can collaborate to create data visualisations aimed at elevating public engagement and effective advocacy, which in turn could bring about real positive change.

Currently I have this particular Power BI Dashboard from just the stocks section of my Nigerian Market Data app.

I have been using them to keep track of my stocks investment and analysis. Especially, the Power BI dashboard one.

Now I am considering making a dashboard for the others: 
1) Nigerian GDP Growth rate,
2) Nigerian Unemployment rate,
3) Nigerian Population,
4) Nigerian Oil Production data,
5) Nigerian Inflation rate,
6) Nigerian CBN Interest rate,
7) Nigeria Purchasing Managers' Index (PMI),
8) Stock indices across the world, and
9) Top 44 countries currency exchange rates with base in Nigerian Naira.

Which are the top four you would like to keep tabs on? 

VLOOKUP won't help you if you need to match two list of names where the first name -- last name positions are often swapped and middle name initial is present in one but absent in the other.

What then can you do?

Use Microsoft's Fuzzy Lookup add-in. You can download it here: Microsoft Research's Fuzzy Lookup

When you are done installing it, you will see it show as a new menu tab in your Excel.

If it's not showing up in yours, you might need to toggle it off and on in the COM Add-in section of Excel Options.

So how do you use it?

Copy the two records side by side in one sheet in Excel.

 Then format each record set as a Table. And you 60% done. 

Just launch the Fuzzy Lookup tool and set the fields you want to match. Set the Similarity Threshold. 

Select the cell to put the output results and click on Go.

And that's all! You'll see it work its magic, saving hours you would have spent doing manual matching.

Think about this: You have a list of products, the list grows with time, and you want your Excel chart of the products versus sales to automatically capture any additional products you add. Or maybe it is a daily sales report, and you want the chart to automatically expand to show the newly added dates.

Well, what you need to create is a dynamic list (proper term is range, but let's go one with the easy fathom "list"). 

It turns this

To this (without you re-making the chart or even touching the chart at all)

And can add life sweetening spice to your Data Validation lists.

Just think of all the magic that would do in some of your reports.

So how do you create these dynamic lists?

Easy. There are two popular ways to create dynamic lists in Excel. One is to use INDEX and COUNTA. The other is to use OFFSET and COUNTA.

Technically, you should choose the INDEX + COUNTA one over OFFSET + COUNTA one as it has better performance. Again, remember the keyword -- technically. Practically, the one you find easier to master is better.

INDEX + COUNTA

For the chart one, I used the INDEX + COUNTA one. 

For the list field I created a named range:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

At the core, it is
=A2:INDEX(A:A,COUNTA(A:A))

What the index part does is to return the cell reference of the last row cell in column A that is populated. 

INDEX(A:A represents a list/array of all the cells in column A while COUNTA(A:A) part instructs Excel to locate the cell in the row position equal to the count of all filled cells in column A.

Now you get how it works.

OFFSET + COUNTA

For the Data Validation List, I used OFFSET + COUNTA

The named range is

=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Again, at its core the formula is

=OFFSET(A2,0,0,COUNTA(A:A)-1,1) 

Easy to understand.

We take A2 as the start or reference point, go zero rows down and zero columns right, then expand the rows (height) downwards by the count of non-empty rows in column A minus one to avoid counting cell A1 (which is used for the field header), then take one column wide (sticking to column A).

The result is

Finally

For the chart, I simply replaced the Legend Entries and Axis Label with the dynamic named range.

And that does the magic!
For the Data Validation List, I simply put =months as the source. (months is the named range I created with the OFFSET + COUNTA formula)

And that's all!

Now you should be a dynamic list guru :)