Forecasting is predicting the future with the help of present and past data. It uses the concept of Exponential Smoothing to predict the future. The Power BI desktop has a very nifty feature of forecasting. This article will describe the process with practical data.
The data has been collected from Wikipedia using the data scraping feature provided in Power BI. I have described here how you can load the data from the web with this feature.
The data I have collected has several years of information on the monthly and annual rainfall of different regions of India. This data can be used to predict the rainfall of those particular regions for the coming years.
The data
I have selected only a single region including West Bengal and Sikkim with rainfall data from 1901 to 2016. The rainfall has been recorded in mm. On the basis of these many years data, lets try to predict what will be the rainfall of next 5 years.
Here is a glimpse of the data.
Creating the line chart
In order to apply the Forecasting feature in the Power BI desktop, we need to create a line chart first. The line chart option is available in the “Visualizations” pane of the application.
Select the “Line chart” option from visualizations and then select appropriate variables from “Fields“.
The next step is selecting the “Year” as the Axis variable and “Annual” rainfall in the Values. Consequently, the line chart will be created.
Forecasting in the Power BI desktop
Now as the line chart is ready we need to create a Forecast for future time points. In “Visualizations” pane, under “Analytics” you can get the option for “Forecast“. But unless your data has at least 60-time points the option will not be available.
Go with the default values of Forecast and click apply. Now forecast for 10 future time points is produced. As in my case each year is individual time point, so forecast for next 10 years will be produced.
The confidence interval is 95% by default. In layman language, out of 100 times the experiment conducted, 95 times the forecast will lie within the interval shown around the forecast values.
But you can see the forecast produced, does not appear to be very realistic. It has no similarity with the historical trend. So, something is wrong here. The seasonality is left to be selected automatically which is not working in the present case.
Seasonality in Forecasting
We need to provide appropriate value to the “Seasonality“. This parameter is the most important in the case of forecasting. So let’s try to adjust these value to get the most accurate result.
Seasonality in time series forecasting refers to a time period during which the data shows some regular and predictable changes. This period may be weeks, months or years with a cyclic pattern.
This cycle we can identify from the line chart we created. If we closely analyze the line chart and zoom it a little, we can notice the line repeats a pattern every 5-6 years period.
So, I will try to create the forecast with seasonality values close to 5 time points.
Checking accuracy of the forecast
To check the performance of the forecast, the forecast tool of the Power BI desktop has one feature “Ignore last”. It simply help us to produce the forecast leaving the last few points as mentioned in this field.
Which means, for this many time period we have both the observed as well as forecast values. Thus we can compare how precise the forecast is.
If we take seasonality of 4 and 6 time points, the forecast has big differences with the observed ones. See the below images. For example, for the year 2011, the actual rainfall is 2418.70mm and the forecast is 2733.56mm.
Again if we set seasonality as 6, again the forecast is very different from the original value.
But if we provide seasonality as 5 we achieve the best forecast with the closest values to the original rainfall. If we again take the example of the year 2011, with seasonality as 6 time points, the rain forecast is 2337.89mm.
The “Format” option allows us to change the style of the forecast report generated. We can change the confidence interval pattern, line pattern and colour etc.
From this day I am starting this new category, where I will update more frequently about what I am working on or any significant thing I learnt recently.
Usually, I post one article weekly with some new application in data science or data analytics. Sometimes if the topic is lengthy or critical, it needs more time.
Often during a busy week where I got little time doing research on a new topic. In such a situation when I missed the Monday evening (usually I post my new blog every Monday evening) it feels very bad. It’s like failing to some commitment made to myself.
So I have decided to post more frequently. Not only on some final product but also to discuss the process, new things learned and mistakes made. Thus it will be like working in front of you sharing each days’ progress.
Sharing the failures are important
I have noticed that the learning process itself is worth sharing with my readers. Since I have started writing online, I used to write articles that solely focused on the topic and the targeted problem. It is a refined product carefully editing mistakes made by me while learning it.
I had the impression that discussing mistakes will be embarrassing. But now I realise that I should write about those mistakes too. In fact, these can be more helpful for those who are also going through the learning process.
Hence this new category “What I am working on?” under the parent category “Life lessons“. The technical article will continue as such and I will try to update you each Monday. But I will update you more frequently about my work and learning process.
Again the inspiration behind starting this more frequent update is “Show your work” by Austin Kleon. It’s really such a nice book. I would like to mention it in every post.
It truly mentioned that sharing your work more frequently is actually a process of getting more clear about your idea. And through frequent update about my progress, I can actually track my progress over time.
Recent work
So here a quick and brief update on my recent work. I am writing a detailed article on using Power BI for forecasting. Forecasting is an important tool that helps us to predict the future based on historical data.
I used to use R or Python for forecasting but recently got familiar with the forecasting tool provided with the Power BI desktop. So thought about applying on a time series data I have.
Soon there will be a post on this topic.
Except this, reading several books and listening audio versions as usual.
Goal seek and solver in Microsoft Excel 2016 are two very important functions. These two help us to perform some back calculations. Among these two, Goal Seek is the simpler one. So let’s start with the Goal Seek function.
I will demonstrate the use of Goal Seek with a very practical example. Every one of us wants to know the future value of their investment amount. And also how much they should invest to reach their goal.
There are lots of online tools available to calculate this. Here we will use the formula for compound interest. We know that our investment either in Bank deposits or the market earns compound interest.
Compound interest means annual interest gets accumulated with the principal amount and next time the interest is calculated on this increased amount.
For example, if we invest Rs 100.00 and gets a 10% interest in the first year, in the next year the principal amount becomes Rs 110.00. So the interest in the next year is 10% of Rs 110.00 that is Rs 11.00, it also gets added with the principal amount (Rs 110.00+11=Rs 121.00) and the process goes on.
See the below screenshot from my excel spreadsheet. It contains the formula for calculating the return from compound interest. Let’s take an example where we assumed an annual interest of 7% and want to know the future value of Rs 10000.00 invested for a period of 10 years.
Use of Goal Seek in Excel 2016
Now what if we want to know how much we need to invest in order to get a return of Rs 25000.00 keeping other conditions same. Here we need to back calculate the investment amount using Goal Seek.
You can find the Goal Seek option in the “What-If Analysis” under the Data tab of Microsoft Excel. It has three fields which we need to fill. See the below figure to understand the process.
We need the Future Value of cell B5 to be 25000. So the “Set Cell” is B5, “To Value” is 25000 and we want to change the value of cell B2.
Now click “OK” to know the investment amount. Now we know that we need to invest Rs 12708.73 (the cell was not set to 2 decimal places) to become Rs 25000.00 in 10 years with an interest rate of 7%.
Again if we want to know how many years it takes to make the same amount Rs 10000.00 to Rs 25000.00 with 7% interest. We need to use Goal Seek and will change the cell B4. See the below image, now we know that we have to keep the amount invested for 14 years.
But the problem with Goal Seek is that it can not be used for changing more than one variables. It is for simple calculations. If we have a more complex situation and needs several variables to be changed to at the same time, we need to use Solver.
So, lets know how Solver functions with an example.
Use of Solver in Excel 2016
“Solver” is not a default application in Excel and comes as an Add-in. You have to add it to make the option appear under the “Data” tab. Follow the steps as shown in the screenshots below to add this Add-in.
Go to “File” and then click “Options” to open Excel Options page.
In Excel Options, go to Add-ins and click on “Go...” to open the window containing list of available Add-ins. Now select “Solver Add-in” and click OK.
Now check if the Solver Add-in has been added under the Data tab.
Application of Solver Add-in
Now to see the application of Solver, let’s take another simple yet practical example. Below I have shown a small stock portfolio created in Excel. Here I have calculated the total invested amount of some stocks with some hypothetical prices. As shown the invested amount has been calculated by multiplying the cost with the stocks quantity.
The total amount stands as Rs. 202660.00. But I want to invest Rs 40000.00. So my goal is to calculate the quantity of some stocks lying within a specified range. And the constraints for this calculation are also mentioned in the below image.
Like Goal Seek, Solver also needs to input the “Objective cell” and “Variable cells” where we want to change the values. See the screenshot below where I have shown how to mention the cells as per our requirement. The value field has 40000 as we want to invest Rs 40000.00 in total.
In the “Add Constraint” field, you need to provide the cell reference and their specific “Constraint“. They need to be provided one by one and finally they are added to the “Subject to the constraints” field. See the image below to understand the process.
Now click “Solve” and then if Solver is able to find a solution for your problem, the next window appears where you need to confirm the change by clicking OK.
Now you have the number of stocks and their costs which you can buy within your budget of Rs 40000.00.
I hope the article will help you understand how to use both Goal Seek and Solver in Excel 2016. Please comment below if you have any questions or doubts regarding the article.
Taking notes can enhance your productivity manifold. Random thoughts and ideas come to our mind during our leisure time. When our mind is stress-free. These are very unique and reflects one’s very own creativity.
If we don’t note them down fresh, they get dumped. So, we need to note them down right then and there. It can act as our extended brain as our brain has limited capacity and makes space for new things deleting the old ones.
Notes thus collected regularly on different experiences of our life works as a resource when you actually in a mood of writing. In my article “Why should you start writing on the internet” I have mentioned how writing can improve our life. Taking notes is also an integral part of this whole writing process.
I recently listened to a youtube video of David Perell where he has explained its importance in his unique style. It was really a nice presentation and moved me so much. No doubt he is a productivity Guru.
Take notes like Kendrick
In this video, he explained the importance of taking notes and how it can help your thought process while writing. He gave the example of famous American rapper Kendrick Lamar.
He is just 33 years old and has won the Grammy Award for the best rap album. He is regarded as the most influential artist of his generation. Kendrick developed the habit of taking notes of everything he observed, memorable lines as well as his opinion since he was 7 years old.
In the later part of his life, this habit helped him a lot in composing his iconic songs. Creative writings are not some struggle done sitting continuously for hours.
Creative ideas come spontaneously in our mind. You can not force it or time it to write them down as you wish.
We can capture our idea by taking notes on paper, computer, capture video or audio, any form you wish. We are in the age where we are carrying a multimedia studio in our pocket i.e. our smartphone. You just need to use them appropriately.
If you are not able to generate idea instantly, just note down a vivid description of any event. It may help you get content ideas later and you can pick useful pieces.
Use Twitter to improve your writing
I have recently opened my Twitter account. Although I have my Facebook page too, I rarely use it. The only active social networking platform I use is Twitter. It is the most preferred hunting ground for writers.
Why Twitter?
Here I can have honest review/feedback on your twit. I try regularly to post updates on my content and help a lot to improve my writing.
It matters most whom you are following, otherwise, you will be brainwashed with irrelevant content only. I carefully select people with the same interest and follow their twits to curate mine.
As learned people say “want to be a good writer? Read the great writers”. Try to write engaging twits which is either useful or entertaining for the readers.
Regarding composing twits again David Perell has some suggestions, which I would also like to follow:
Write compressed twits. Your idea should be accommodated within a twit length.
Clarity and simplicity are important. Twit is different from books, your readers will skip it while browsing if it is not easy to understand.
Clickable content (hashtags) should not be more than one. And that one also if very essential
There should be some clear message. Be aware not to self promote rather invent yourself through it.
Writing on the internet has multiple benefits. It can bring opportunities you have never imagined. Here I am going to share my experience as a data science blogger for the last few years. And try my best to explain the perceived positive changes which may make you realize why should you also take the plunge.
My purpose was to document different skills I have learned or still learning in this field. There was a sequence of events that motivated me to start this blog. Among which a book played a very important role.
I have been an avid reader. One of my favourite books is “Show your work” by Austine Kleon. Some advice, suggestions from this book made a lazy person like me inspired enough to take writing seriously.
I should have written down all those motivating thoughts which helped me take one of the biggest decision of my life. But I was not confident enough about expressing myself at that time.
Two years down the line, with a fair amount of experience of blogging, I am finally writing this. Anytime if I feel out of focus, this would keep me motivated.
From now on I am including another category named “Life Lessons” in my blog. Where I would write on several life lessons I gathered since I started this journey.
It has given me so much, changed my life so much during this last one & half year. As a writer, you may achieve many accolades, in the long run. But you can notice small changes this habit brings in your life when you are consistent enough.
So not only the destination, the journey itself is so much rewarding. Enjoy the journey and don’t focus so much on the destination. Smell the roses, talk to the strangers in your journey.
In Brandon Sanderson’s language, there is ” life before death, strength before weakness, journey before destination”.
Gives shape to your thoughts
I think the most important thing we achieve while we write about our own ideas is we get clarity on them. Many times ideas just appear in our mind and then just vanish.
Ideas are spontaneous. These are the most valuable assets and the most original things we own. They are abstract in nature and we tend to ignore them.
We should grow the habit of writing them down. In this process, we start discussing it with ourselves. And the idea starts to take shape. What was just a spark in the mind and volatile in nature becomes a concrete concept.
This is what mentioned by Austin Kleon in his book “Show Your Work“. It is just writing diaries in earlier days. And if you notice it was a hobby of and consistently done by many famous people. The “Kheror Khata” of the Maestro Satyajit Ray is a good example of taking random notes. That diary is now part of the National Digital Library developed by IIT, Kharagpur.
Such an idea when documented after thorough research, they become unique articles. Articles that contain some genuinely helpful/entertaining content for the readers.
Building connection with like minded people
When you put your content on the internet it starts speaking on behalf of you. Which means pesonally you may be an introvert person and have a very limited network. In that case, the best way to promote your ideas to put them on the internet.
The Internet has no barrier, no boundary for countries and reaches every corner of the world. When your ideas start to reach such a vast audience, the opportunities are endless.
The immediate benefit may be people with same interest start to contact you. You can cosider it as the first stepping stone of your online venture. This may be a baby step towards your goal but has immense potential.
Without online publishing your content you may never come to know that there are so many people who also think like you.
Consider it as your online resume
Imagine the situation if you are a job seeker and your employer already knows about your expertise from your blog. You have already put all of your learning at one place and for a student it can help him to acquire first project from his/her dream organization.
Those days are gone when you need to wait for your final product and then only you let the world know about your creation. In this age of Internet and social network platforms, you can even showcase the process of your creation.
People are continuously writing on internet posting every bits and pieces of the process, the tool they use, rough work even unfinished product and failed product.
The purpose is you are building a group of followers. They are organic and have a genuine interest in what you are doing. Anytime in life, you want to make your passion your profession, you already have a target audience.
Make your everyday count
Write at least few lines everyday. It will bring a feeling of being productive. You have not wasted that day and have done something towards your growth.
So whenever you get some time, sit before your computer, collect your thoughts and write them down. Our lazy part of mind will resist you saying it is not worth your time. Ask yourself is there anything more worthy to do?
Give at least half an hour everyday. It may appear insignificant at first. But consider it baby steps towards your goal. Single strokes of brush gradually complete the whole painting.
Get over the stupid excuses
There are several mental blocks that resist us while we start writing on the internet for the first time. I have also wasted a lot of time over these obstacles which now I understand as very stupid.
What will other think?
This is the most regressive thing that keeps us behind in every walks of our lives. Many of us will say that we don’t bother about what others think but still has this fear subconsciously.
And believe me, no one cares. Yes, you read it right, no one cares what you are writing there. Take my example here. I am writing for more than one year now and really no one has nothing to do with it.
It only interests those who are also interested in the topic of data science, search it on the internet and reaches my website. They even contact me and we share topics of shared interest.
I am not an expert of anything!
This fear is also baseless. You dont have to be an expert. You can share experince of learning anything and even the mistakes you made while learning anything.
Always remember a beginner can always guide another beginner sharing their experiences. When anyone becomes an expert on anything he forgets the struggle he faced at the initial stage. And thus his advice usually can not guide someone who just started.
A word of caution here, dont try to pretend being an expert. It is a big mistake many times a beginner commits. Readers can easily find out such writing not to be genuine.
Which topic should I write?
Another big hurdle while starting to write on the internet. Here I would like to mention the comment made by famous writer David Perell.
He makes choosing the topic for your writing very easy. Don’t try to find out a topic that interests others rather pick the topic which simply interests you.
In this way, you will never run out of content ideas. You will also continue to learn new things to improve your expertise on your favourite topic.
Try to develop your own concept on the topic. Then explain it in your own language including your ideas. Thus your writing will be your intellectual property and unique.
I am not good at English!
English is the language you can reach the maximum number of readers with. But not everyone there is a native English speaker. And that is why this fear is genuine.
But the good thing is you don’t need to be very good at English writing. Just good enough to express yourself. No one cares if you have made small grammatical mistakes in doing so.
I am not yet fluent in speaking English. But does that matter much? Are you not able to understand what I want to convey here? I may have made several grammatical mistakes and incorrect use of words but I think I still have managed to explain myself.
Thats all you need to do while you just start writing. And regular practice will do the rest. You can easily find the difference between this article and those I have written in 2019.
So don’t let this fear bog you down or resist you taking the first step.
Finally, enjoy the journey of writing on the internet
Your goal may be endless and you want to achieve many things. But when you are just starting, dont think much about your final goals. It may bring frustration. As at initial stages you may not notice any significant things happening and still you need to continue your activity.
So rather you should enjoy the journey of creativity and learning new things. Writing online is a productive thing. And when you continue with this activity you are growing numerous good habits.
Like you are growing the habit of learning new things. You need to research some topic before you write a convincing article on it. And in this process, you acquire a good knowledge of the topic.
Conclusion
Life becomes stagnant when someone stops improving himself. You start to learn new things and soon you realize how much is there yet to learn. You regret the time wasted doing unproductive things.
Since I have started writing on the internet, the single big change happened is I have become way more productive than I was before.
And among the small things, many skills I acquired to improve my content quality. I am now managing my time more effectively. I am more focused and always eager to learn new things.
Usually, we lose interest in upgrading ourselves as there is no one to appreciate it. Writing on the internet removes this barrier and brings you a global audience. And suddenly you have a good purpose to enrich your knowledge.
I hope that I have put enough points to convince you to start writing on the internet. Believe me, there are a lot more which I am not able to put here perhaps for lack of expression. I will continue to update this post as and when I am able to put them into words.
If you find this article helpful, let me know. It will certainly encourage me to bring more topics like this. I will look for your comments and ideas regarding the post.
Create new column from existing column in Power BI is very useful when we want to perform a particular analysis. Many times the new column requires clipping a string part from an existing column.
Such a situation I faced a few days back and was looking for a solution. And this is when I again realised the power of “Power Query“. This article is to share the same trick with you.
If you are new to Power BI, then I would suggest going through this article for a quick idea of its free version, Power BI desktop. It has numerous feature for data manipulation, transformation, visualization, report preparation etc.
Here are some popular application of Power BI as a Business Intelligence tool.
This article covers another super useful feature of Power BI. Adding a new column derived from the existing columns are very common in data analytics. It may be required as an intermediate step of data analysis or fetching some specific information.
For example, we may need only the month or day from the date column, or only the user-id from the email-id list etc.
In this article I will demonstrate the process using the data sets related to India’s state-wise crop production and rainfall data.
Lets start the process step by step and see how I have done this.
Use of “Add Column” and “Transform” options
Power BI desktop offers two options for extracting information from an existing column. Both the options namely “Add column” and “Transform” has different purposes altogether.
Add column option adds a new column extracting the desired information from the selected column. Whereas the Transform option replaces the existing information with the extracted text.
Here our purpose is to create new column from existing column in Power BI. So let’s explore the “Add Column” feature and the options it offers.
Create new column from existing column Power BI with “Add column” option
First of all, you need to open the “Power Query Editor” by clicking “Transform data" from the Power BI desktop. Here you will get the option “Extract” under the “Add column” tab as shown in the below images.
Extracting the “Length“
This option is to fetch the length of any particular string and populate the new column. See in the below example, I have created a new column with the length of the state names from “State_Name” column.
The power query associated with this feature is as given below. The M language is very easy to understand. You can make necessary changes here itself if you want.
= Table.AddColumn(#"Changed Type", "Length", each Text.Length([State_Name]), Int64.Type)
Extracting the “First Characters“
If we select the “First Character” option, then as the name suggests, it extracts as many characters as we want from the start. As shown in the below image, upon clicking the option, a new window appears asking the number of characters you want to keep from the first.
As a result, the new column named “First Characters” contains the first few characters of our choice.
Extracting “Last Characters“
In the same way, we can extract the last characters as we need. See the below image, as we select the option "Last Characters", again it will prompt for the number of characters we wish to fetch from the last of the selected column.
As we have provided 7 in the window asking the number of characters, it has extracted a matching number of characters and populated the column “Last Characters”.
Extract “Text Range“
This option offers to select strings from exact location you want. You can select starting index and number of characters from the starting index. See the bellow example where I wished to extract only the string “Nicobar”.
Keeping this in mind, I have provided 12 as the starting index and 7 as the number of characters from the 12th character. The result is the column “Text Range” has been populated with the string “Nicobar” as we wanted.
Extracting using delimiters
Another very useful feature is using delimiters for extracting necessary information. It has again three options for using delimiters, which are:
Text Before Delimiter
Text After Delimiter
Text Between Delimiter
The image below demonstrates the use of the first two options. As the column “State_Name” has only one delimiter i.e. the blank space between the words, so in both the cases, I have used this delimiter only.
You can clearly observe differences between the outputs here.
The script for executing this operation is given below.
= Table.AddColumn(#"Removed Columns", "Text After Delimiter", each Text.AfterDelimiter([State_Name], " "), type text)
Below is the example where we need to extract text between the delimiters. The process is same as before.
The code is as below.
= Table.AddColumn(#"Inserted Text After Delimiter", "Text Between Delimiters", each Text.BetweenDelimiters([State_Name], " ", " "), type text)
Another example with different delimiters
Below is another example where you have some delimiter other than the blank space. For example one of the columns in the data table has a range of years. And the years separated with a “-“.
Now if we use this in both cases of text before and text after delimiter, the results are as in the below image.
Use of “Conditional Column“
This is another powerful feature of the Power BI desktop. Where we create a new column applying some conditions to another column.
For example, in the case of the agricultural data, the crop cover of different states have different percentages. And I wish to create 6 classes using these percentage values.
First, open the Power Query Editor and click the “Conditional Column” option under the “Add column” tab. You will see the window as in the below image.
The classes will be as below
Class I: crop cover with <1%
Class II: crop cover 1-2%
Class III: crop cover 2-3%
Class IV: crop cover 3-4%
Class V: crop cover 4-5% and
Class VI: crop cover >5%
See the resultant column created with the class information as we desired.
Using DAX to create new column from existing column Power BI
We can also use DAX for creating columns with substrings. The option is available under the “Table tools” in Power BI desktop. See the image below.
Now we need to write the DAX expressions for the exact substring you want to populate the column with. See the image below, I have demonstrated few DAX for creating substring from a column values.
Comparing to the original values you can easily understand how the expressions work to extract the required information.
Combining values of different columns
This is the last function which we will discuss in this article. Unlike fetching a part of the column values, we may need to combine the values of two columns. Such situation may arise in order to create an unique id for creating keys too.
Likewise here my purpose was to combine the state name and corresponding districts to get a unique column. I have used the COMBINEDVALUES() function of DAX to achieve the goal.
See the below image. I have demonstrated the whole process taking screenshots from my Power BI desktop.
I have tried to cover the steps in a single image. The original two column values as well as the combined value columns are shown side by side so that you can compare the result.
Final words
In this blog I have covered several options available in Power BI desktop for creating new column extracting values from other columns. We frequently face such situation where we need to create such columns in order to get desired analysis results or visualization.
I hope the theory explained along with the detailed screenshots will help you understand all the steps easily. In case of any doubt please mention them in the comment below. I would like to answer them.