Last week, I got a call from a past participant of our Power BI training regarding an issue he was battling. We provide after training support for all our training, which is one huge value most other training providers don't provide, especially those who don't have in-house dedicated trainers with deep consulting expertise.

He has system generated reports that look like the following:



Which when you bring into Power BI, still look like that.



How then do you turn them into this:



... but in Power BI.

With help from Matt Allington's amazing blog, I got what will do the magic.

We create Custom queries to grab and store the region and company before promoting the actual header row to being the header.

Click on the fx right beside the formula edit space to add a query step.



We then add two custom queries, one to grab the region and another to grab the company name.

The first is: 

= Record.Field(Source{[Item="Sheet1",Kind="Sheet"]}[Data]{0},"Column8")

Which is essentially telling Power Query to grab the first cell entry on the Column named Column8. For the part inside the Record.Field but before the {0}, just copy paste the last query in there.




We do another custom query to grab the company name.



Now that we have grabbed and stored those cell entries, we can now go back to the source table and do our normal header row promotion.

To get back to the source table, just do another add step but paste in it the exact query in the step before our first custom query.



What is left is to create Custom Columns with those saved values.





And that was it! Just move them to the beginning of the table.



Okay, that's not entirely true. That wasn't just all we did. He had many of those system generated files and already autosave them in a folder. So we needed to import the folder and have Power Query combine the files but first do these transformations before combining the files.

However, this good enough for today's post. Here's the Power BI practice file: https://urbizedge.blob.core.windows.net/urbizedge/Sample%20Data%20for%20Power%20Query%20custom%20cell%20entries%20grabbing.pbix 

Enjoy!
Today, I'll be sharing with you the amazing Group By feature in Power Query, and the DAX equivalent I typically use.

I have a database of stocks on the Nigerian Stock Exchange, tracking their daily price among other important investment analysis metrics going as far back as 1998. BTW, don't ask me for the data 'cos I won't give it away. It's at the core of our www.nigeriamarketdata.com app and Nigerian Market Data office app.



I want to see the range of performance (volatility) of the stocks over the last 3 years. I have pulled the last 3 years data into Power BI.



How do I get the minimum price and maximum price of each stock in that 3 year period?

Very easy. I click on Transform menu and Group By. Easily, using the user interactive window that comes up, I set the very group by transformation I want.


And voila! It is done.



How about achieving same using DAX?



Then meet SUMMARIZE. It does it nicely.

I click on Modeling menu and New Table. I then type in SUMMARIZE formula

Group_By_Table = SUMMARIZE(Stocks,Stocks[Company],"Minimum Price",MIN(Stocks[Price]),"Maximum Price",max(Stocks[Price]))


Now I can do further analysis like check which has been the most volatile stock and which has been the least volatile stock.


The first time I was asked a question around this was in 2016 at a training I had for eHealth Africa in Kano. A couple of the finance managers wanted to be able to convert money amounts to words with Naira and kobo in appropriate places.

Then two weeks ago in Abuja, I got asked the same question by finance team at HSDF and it became obvious to me that it is a problem many people would like easy steps to accomplishing. Many people who come across solutions online still face roadblocks in implementing the solution satisfactorily. 

Well, as a patriotic Nigerian, I have come in my shining armor to give every Nigerian an almost plug and play solution with easy to understand steps.

Solution (Warri style)
Download these two Excel files that I saved as add-in files: https://urbizedge.blob.core.windows.net/urbizedge/Numbers2Words.xlam and https://urbizedge.blob.core.windows.net/urbizedge/Numbers2WordsUSD.xlam

Then go to File, Options, Add-ins and click on Go in front of Manage Excel Add-ins.




Click on browse to pick the files (one at a time). 




Ensure they are ticked as part of enabled/available add-ins.



And that's all! Now, go and type less henceforth.




Explanation (Brother Jero style)
What we are trying to achieve is a simple task called User Defined Function -- creating our own formulas that the Engineers at Microsoft didn't include in Excel. Unfortunately, the process is not straightforward. We would have to not get on the wrong side of Amope (VBA).



We would use a modified version of this boilerplate from Microsoft with some extra tweaks from Ablebits.



Here's the one for Naira and Kobo:

Option Explicit
'Main Function
Function SpellNumberNGN(ByVal MyNumber)
    Dim Naira, Kobo, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "

    MyNumber = Trim(Str(MyNumber))
    DecimalPlace = InStr(MyNumber, ".")
    If DecimalPlace > 0 Then
        Kobo = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then Naira = Temp & Place(Count) & Naira
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    Select Case Naira
        Case ""
            Naira = ""
        Case "One"
            Naira = "One Naira"
         Case Else
            Naira = Naira & " Naira"
    End Select
    Select Case Kobo
        Case ""
            Kobo = ""
        Case "One"
            Kobo = " and One Kobo"
              Case Else
            Kobo = " and " & Kobo & " Kobo"
    End Select
    SpellNumberNGN = Naira & Kobo
End Function

Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    ' Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function

Function GetTens(TensText)
    Dim Result As String
    Result = "" ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
        Select Case Val(TensText)
            Case 10: Result = "Ten"
            Case 11: Result = "Eleven"
            Case 12: Result = "Twelve"
            Case 13: Result = "Thirteen"
            Case 14: Result = "Fourteen"
            Case 15: Result = "Fifteen"
            Case 16: Result = "Sixteen"
            Case 17: Result = "Seventeen"
            Case 18: Result = "Eighteen"
            Case 19: Result = "Nineteen"
            Case Else
        End Select
    Else ' If value between 20-99...
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "Twenty "
            Case 3: Result = "Thirty "
            Case 4: Result = "Forty "
            Case 5: Result = "Fifty "
            Case 6: Result = "Sixty "
            Case 7: Result = "Seventy "
            Case 8: Result = "Eighty "
            Case 9: Result = "Ninety "
            Case Else
        End Select
        Result = Result & GetDigit _
            (Right(TensText, 1))  ' Retrieve ones place.
    End If
    GetTens = Result
End Function

Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    End Select
End Function


You will copy this. Open an empty/new Excel file, press Alt + F11. The VBA Editor will open. On the menu, select Insert and click on Module. Then paste the code above in it.




Save (CTRL + S), but choose Excel Add-in as the save as type. Importantly, do not change the default folder Excel will automatically change to for saving for the file. If you do this, then you are OYO.



Now we are almost there.

Repeat same steps for dollar and cents (if you need it). Here is the code for that:


Option Explicit
'Main Function
Function SpellNumberUSD(ByVal MyNumber)
    Dim Dollars, Cents, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "

    MyNumber = Trim(Str(MyNumber))
    DecimalPlace = InStr(MyNumber, ".")
    If DecimalPlace > 0 Then
        Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    Select Case Dollars
        Case ""
            Dollars = "No Dollars"
        Case "One"
            Dollars = "One Dollar"
         Case Else
            Dollars = Dollars & " Dollars"
    End Select
    Select Case Cents
        Case ""
            Cents = ""
        Case "One"
            Cents = " and One Cent"
              Case Else
            Cents = " and " & Cents & " Cents"
    End Select
    SpellNumberUSD = Dollars & Cents
End Function

Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    ' Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function

Function GetTens(TensText)
    Dim Result As String
    Result = "" ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
        Select Case Val(TensText)
            Case 10: Result = "Ten"
            Case 11: Result = "Eleven"
            Case 12: Result = "Twelve"
            Case 13: Result = "Thirteen"
            Case 14: Result = "Fourteen"
            Case 15: Result = "Fifteen"
            Case 16: Result = "Sixteen"
            Case 17: Result = "Seventeen"
            Case 18: Result = "Eighteen"
            Case 19: Result = "Nineteen"
            Case Else
        End Select
    Else ' If value between 20-99...
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "Twenty "
            Case 3: Result = "Thirty "
            Case 4: Result = "Forty "
            Case 5: Result = "Fifty "
            Case 6: Result = "Sixty "
            Case 7: Result = "Seventy "
            Case 8: Result = "Eighty "
            Case 9: Result = "Ninety "
            Case Else
        End Select
        Result = Result & GetDigit _
            (Right(TensText, 1))  ' Retrieve ones place.
    End If
    GetTens = Result
End Function

Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    End Select
End Function


And now you do the steps I put under the solution section, except that you'll see the add-ins there without needing to browse to them. Ensure they are ticked.





And from now on, you'll have the two formulas that can convert numbers to words (naira/kobo and dollars/cents). 




Again, I say to you: enjoy!
I originally shared this with folks in the Investment Whatsapp group. I have been more active there than on this blog. Unfortunately, the group is full so I can't share a joining link with you all anymore.

So back to today's post. Important to note that the promo started on Thursday and is ending today.


The promo access link is https://www.realvision.com/register/tv/onedollar/1year 

And below is my explanation of why I think it is well worth it. Sharing text for text (with minor edits of typo etc), what I wrote in the Whatsapp group.

Got something interesting to share with the house, something I took advantage of.

As we all know how hard it is to get expert opinions, commentaries and analyses from our Nigerian experts.

Unlike the UK, US, Australia and even India folks who share on blogs, write books, give frequent commentaries on the markets they actively trade/invest in. Especially, those who manage funds and other people's money. We don't have that luxury to enjoy here in Nigeria.

And as we all know that one cannot learn from oneself but from others and diverse opinions and especially from experts who live and breath the market, I have been actively immersing myself in online stocks, bonds and general investment communities. Everyday, I go through SeekingAlpha.com and use a couple of other free to access communities.

Good news, one of the paid communities I use when I can afford it or get a promo is currently running a promo. RealVision.com

From Thursday till this Saturday, they are slashing their subscription fee from $180 to $1 a year. 

And as per testimonial, I am one. I have learned immensely from their interview series where global fund managers, chief investment officers of banks and investment houses, and market analysts share unrestrained deep insights in their area of expertise and the investment moves/decisions they are currently making.

One aspect of it that delights me, is hearing people who have been managing investments since 1970s (through periods when the US markets/economy look in some ways like ours -- high inflation, market manipulations, low regulation, focus on bonds more than equities etc) share how things have changed and what investment moves worked then and what works now. 

I also learned a lot about the humility pro investors/traders have and the life-long learning that is the only way to not getting whacked by the market. And that there is just so so very much to learn and apply.

I also learned a lot about how to place the macro-economic happenings in context and tie things together across interest rate, inflation, bond, commodities, equities, real estate and alt investments.

The promo access link is https://www.realvision.com/register/tv/onedollar/1year 

Enjoy!
I once facilitated a Power BI training where the participants needed to generate a new table from the sales transaction data (that comes from the ERP) in such a way that they would see all the sales regions, products, Year-Month and total sales quantity.

They already have a table of targets by products for each region. 

Ordinarily, we could have just created a relationship between the transactions data and the target table and leveraged that with measures to create whatever type of report needed. But as it was a training and they needed to experience all the different ways of doing a particular task, we decided to create a table that will extract all the regions, products by regions and total sales volume present in the sales transaction data.

To achieve that, we used the DAX formula: SUMMARIZE

So here's what the sample data I am going to use look like:



And the new table Formula I'll enter is:


Branch_Product_Sales = SUMMARIZE(
'Pizza Sales Data',
'Pizza Sales Data'[Branch],
'Pizza Sales Data'[Pizza Sold],
"Total Sales Quantity",sum('Pizza Sales Data'[Quantity])
)



You can read up on the official documentation of SUMMARIZE: https://docs.microsoft.com/en-us/dax/summarize-function-dax 

In essence, how the syntax works is you put in the table name, the columns you want to pick unique items from (combines across the columns, though, which makes it show all the different products for the different branches repeating branches as many times as necessary to show products in that branch) and you add the aggregation formula to run on a column with the values to do a computation on (indicating the name to give the generated column).

There's a more complex use of SUMMARIZE that involves using ROLLUP and/or ROLLUPGROUP. You can get a sense of what they do by going through the already given documentation link.

Here's a short video to demonstrate the formula: https://youtu.be/X8mqjSQ9gOo




Enjoy!

And to join our Power BI training class, visit https://www.urbizedge.com/powerbi or call 01-6311885 or email team@urbizedge.com
One common situation most users of Excel face in making recurrent reports -- daily reports, weekly reports, monthly reports etc -- is that of updating the raw data part of the report with the new data for the period they are creating report for.

As simple as this looks, it is often a boring, repetitive and time consuming task. It could mean having to copy and paste data from the new source data files every day. For some people who get data from different sources (branches, sales people, departments etc), that coping and pasting can easily become a 20 mins task with potentials for human errors.

How do you set up your report to automatically pull in new data from source files? PowerQuery is the answer.

And if you are wondering if this will work for data that you get over emails daily/weekly/monthly. Yes, it works for them too.

The major step is to always save the source files in a dedicated folder. Have a folder on your computer than will hold only those source data files. The files can be Excel files or text files (CSV, TSV etc).


Also, ensure that the files have same table column structure -- same table headers arranged in a consistent manner.

With all these done, you are half way.

In demonstrating this, I have a folder where I put in sales data from different branches of a Pizza restaurant. Everyday, I am required to do sales reports, trend analysis and target/KPI dashboards from all these sales data for management. Before, I would copy paste all the source data in a particular sheet on my daily report template. This could take between 5 to 15 mins depending on how tired/refreshed I was and if any interruption that makes me forget where I stopped.




Now with PowerQuery, all those boring aspect of the reports are fully automated away.

Instead of copying and pasting the content of the different source files into Excel, I go to Data menu, Get & Transform, Get Data, From File and From Folder.


Browse to the folder destination and select it.


You'll see all the files in the folder. Select Combine and Load, in the Combine options.


Pick the sheet that's got the data. That sheet name must be consistent across all the source data files.


Wait a couple of seconds while PowerQuery does its magic.

Notice how all the branches data have been pulled in.


And that's not the best part. The whole essence of my proposing PowerQuery is to handle tomorrow's source data and the days after.

So how does it work?

Very easy.

Just replaced the data files in the folder with tomorrow's data files. Then right click anywhere in the combined table data and select Refresh.

What if there's a new branch that just sent me their data after I had done all of these, what should I do to bring in that new branch's data? 

Again, very easy.

Include/save the new branch's data file in the dedicated folder. And click on Refresh in the combined table.

See an example below. 



We launched a new branch in Lokoja and after creating my report for the day, I suddenly got an email of the sales data for that branch and asked to include it in the daily report.

I simply add the branch data file to the dedicated folder and click on Refresh in my already existing combined table.



And that's all!

No more zombie-like copying and pasting.

You can watch the short video demonstration: https://youtu.be/mKEHb0OWuP8



Enjoy!

For all your Excel training needs, you can check us out at https://www.urbizedge.com/Excel or call 01-6311885 or email team@urbizedge.com


If there exists a book of ten commandments for Power BI, one of the commandments will be -- always have a date table.

A date table is a table that has just dates, and maybe any derivative of a date (month, quarter, year etc.)

Why do you need a date table?

Two easy/quick reasons:

  1. You have many tables with date columns in each. It is better to have one table to include all the date periods you need and be related to all these other tables.
  2. Even if you have just one table, as long as that table can have date gaps or 1900 for some records or 2090 for some records; you'll want to have your own better controlled date table rather than battle endlessly with wrong date entries in source data table. This is common when old data are migrated from a legacy tool.
So how do you create a DATE table easily in Power BI?

The answer is CALENDAR.

If I know how far back I want to go, then I would just specify that date as the start date and today as the end date.

Some other times, I would let CALENDAR use FIRSTDATE to get start date and LASTDATE to get end date.

Other times, I would specify a specific date and specify a specific end date (not TODAY, could be a future date).

For this illustration, I would use a specific start date and TODAY as the end date. Power BI will always increment the dates everyday -- that is the magic of putting TODAY as end date.


Calendar = CALENDAR(DATE(2016,01,01),TODAY())



Notice I used DATE to put in the specific date.


You can watch the YouTube tutorial: https://youtu.be/oodi9Vz0eeQ



Enjoy!

And to join our training class, visit https://www.urbizedge.com/powerbi