Reading Notes #325

Suggestion of the week

• 6 Presentation Mistakes Every Professional Should Avoid (Mike Pacchione) - Very interesting post about mistakes that most (read all) of us do, and of course how we could avoid them.

Cloud

• Dependency Injection and ILogger in Azure Functions (Rory Primrose) - Nice post that explains how to do logging with ILogger in Azure functions.

Programming

• About Blockchain (Clemens Vasters) - Blockchain; the what, the how, the why and probably all your questions about it are answered in this post.
• Connecting Your Azure Bots with QnA Maker Services (Abhijit Jana) - Nice post to get started with the bot framework and learn how to test your prototype.
• Join Microsoft Build Live for the ultimate digital experience (Microsoft Azure) - It's this week reserve your days!
• Keep VS Code from Becoming an IDE (Ryan Palo) - Great finally! I super happy to see I'm not the only one doing that. Now what we need is a way to quickly change setups... Going from Markdown editor to Azure C# Function.
• VS Code can do that?! (Burke Holland and Sarah Drasner) - Pretty cool little tips and tricks with vscode.

Data

• Power BI Desktop April Feature Summary (Amanda Cofsky) - Check out all the new features from April release.

Miscellaneous

• How Can You Be Sure Someone Has True Leadership Skills? Look for These 2 Rare Signs (Marcel Schwantes) - Interesting post. Its True, we all have our stories.

Books

• The ONE Thing The Surprisingly Simple Truth Behind Extraordinary Results
  
  Authors Gary Keller, Jay Papasan
  That book was fantastic, and I really mean it. It helps on so many points! Professional, personal, social... It must be in your library.
  ISBN: 978-1885167774

PowerBI and Microsoft Azure Consumption

Recently, I needed to check and compare Azure consumption for a client. What a repetitive task: download the csv files from the Azure billing portal, open it in Excel to clean/merge/customize it… Would it be great if it could be easily done? Well, it is! Power BI is the perfect tool to do that (and a lot more).  In this post, I will explain how I created my Power Query and solved different problem I encountered in my journey.

The Goal

I want PowerBI to read (dynamically) all csv files in a folder and updates all my charts and graph, so I can share them easily why my clients.

The Tools

To create Power Queries, you can use the new Power BI Desktop available online for free or Excel. With Excel 2016, the Power query editor tools is included, for the previous version you need to install the Microsoft Power Query for Excel add-in. In both cases, many tutorials explain how to get started with these tools (see the references at the end of this post).

The Problem

Creating our query should be pretty straight forward, since we can create a Power Query by selecting a folder as a source.

The problem is that our file contains three types of records: Provisioning Status, Statement, and Daily Usage. These “tables” are very different and don’t have the same number of columns. This is why when we try to merge them; we got some Error.

The Solution

The way to solve this problem is to create a function that will parse one file to extract one recordset, and call that function for all the file in the folder.

Note:

The simplest way to get started is to work with one file, then convert it to a function. The way to that is to replace the path of the csv file by a variable that will be passed as a parameter: (filePath) =>.

To keep the code as simple as possible, I kept the function and the looping query separated, but they can be merged in only query.

Extract “Daily Usage”

Here are the queries to extract the Daily Usage (third recordSet) from the csv file and some code description.

 // -- fcnCleanOneCSV_v2 ----------------------------------------

(filePath) =>
let
   fnRawFileContents = (fullpath as text) as table =>
let
   Value = Table.FromList(Lines.FromBinary(File.Contents(fullpath)),Splitter.SplitByNothing())
in Value,

   Source = fnRawFileContents(filePath),
   #"Daily Usage Row" = Table.SelectRows(Source, each Text.Contains([Column1], "Daily Usage")),
   #"DailyPosition" = Table.PositionOf(Source, #"Daily Usage Row" {0}),
   #"TopRemoved" = Table.Skip(Source, (DailyPosition + 1)),
   #"Result" = Table.PromoteHeaders(TopRemoved)
in 
   Result

The first part is to load the content of the file as a one column table. Then DailyPosition is used to store the position where Daily Usage data starts. This value is used in Table.Skip(Source, (DailyPosition + 1)) to keep only the rows after, since Daily usage is the last recordSet it works perfectly.

 //== Process Folder CSV_v2 for Daily Usage==============================

let
   Source = Folder.Files("C:\Azure_Consumption_demo\CSV_v2\"),
   MergedColumns = Table.CombineColumns(Source,{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
   RemovedOtherColumns = Table.SelectColumns(MergedColumns,{"Merged"}),
   #"Results" = Table.AddColumn(RemovedOtherColumns , "GetCsvs", each fcnCleanOneCSV_v2([Merged])),
   #"Removed Columns" = Table.RemoveColumns(Results,{"Merged"}),
   #"Expanded GetCsvs" = Table.ExpandTableColumn(#"Removed Columns", "GetCsvs", {"Usage Date,Meter Category,Meter Id,Meter Sub-category,Meter Name,Meter Region,Unit,Consumed Quantity,Resource Location,Consumed Service,Resource Group,Instance Id,Tags,Additional Info,Service Info 1,Service Info 2"}, {"Usage Date,Meter Category,Meter Id,Meter Sub-category,Meter Name,Meter Region,Unit,Consumed Quantity,Resource Location,Consumed Service,Resource Group,Instance Id,Tags,Additional Info,Service Info 1,Service Info 2"}),


   #"Demoted Headers" = Table.DemoteHeaders(#"Expanded GetCsvs"),
   #"Split Column by Delimiter" = Table.SplitColumn(#"Demoted Headers","Column1",Splitter.SplitTextByDelimiter(","),{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16"}),
   #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}, {"Column1.13", type text}, {"Column1.14", type text}, {"Column1.15", type text}, {"Column1.16", type text}}),
   #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),
   #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Usage Date", type date}, {"Meter Region", type text}}),
   #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","""","",Replacer.ReplaceText,{"Meter Category", "Meter Id", "Meter Sub-category", "Meter Name", "Meter Region", "Unit", "Resource Location", "Consumed Service", "Instance Id", "Tags", "Additional Info", "Service Info 1", "Service Info 2"}),
   #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Consumed Quantity", type number}})
in
  #"Changed Type2"

From row 1 to 6, we get all the file in the folder then combine columns to get a full path for each file. We then pass that to our function previously defined. With the command Table.SplitColumn, on line 11, we re-built the result as a table with multiple columns.
The rest of the query is to clean-up the result by changing the column’s type or removing undesired character.

Extract “Statement”

To get the Statement recordSet, it’s the same thing except that we will Table.Range, since the rows that we are looking for are between Provisioning Status and Daily Usage.

//== fcnGetStatement ========================================== 

(filePath) =>
let
   fnRawFileContents = (fullpath as text) as table =>
let
   Value = Table.FromList(Lines.FromBinary(File.Contents(fullpath)),Splitter.SplitByNothing())
in Value,

    Source = fnRawFileContents(filePath),
    #"Daily Usage Row" = Table.SelectRows(Source, each Text.Contains([Column1], "Daily Usage")),
    #"DailyPosition" = Table.PositionOf(Source, #"Daily Usage Row" {0}),
    #"Statement Row" = Table.SelectRows(Source, each Text.Contains([Column1], "Statement")),
    #"StatementPosition" = Table.PositionOf(Source, #"Statement Row" {0}),
    #"SelectedRows" = Table.Range(Source,(StatementPosition+1),(DailyPosition - StatementPosition )-2),
    #"Result" = Table.PromoteHeaders(SelectedRows)
in
    Result

And once again we loop through every file and do some clean-up.

//== Query Statements ========================================

let
    Source = Folder.Files("C:\Azure_Consumption_demo\CSV_v2\"),
    MergedColumns = Table.CombineColumns(Source,{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    RemovedOtherColumns = Table.SelectColumns(MergedColumns,{"Merged"}),
    #"Results" = Table.AddColumn(RemovedOtherColumns , "GetCsvs", each fcnGetStatement([Merged])),
    #"Removed Columns" = Table.RemoveColumns(Results,{"Merged"}),
    #"Expanded GetCsvs" = Table.ExpandTableColumn(#"Removed Columns", "GetCsvs", {"Billing Period,Meter Category,Meter Sub-category,Meter Name,Meter Region,SKU,Unit,Consumed Quantity,Included Quantity,Within Commitment,Overage Quantity,Currency,Overage,Commitment Rate,Rate,Value"}, {"Billing Period,Meter Category,Meter Sub-category,Meter Name,Meter Region,SKU,Unit,Consumed Quantity,Included Quantity,Within Commitment,Overage Quantity,Currency,Overage,Commitment Rate,Rate,Value"}),


    #"Demoted Headers" = Table.DemoteHeaders(#"Expanded GetCsvs"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Demoted Headers","Column1",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter"),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","""","",Replacer.ReplaceText,{"Meter Category", "Meter Sub-category", "Meter Name", "Meter Region", "SKU", "Unit"})
in
    #"Replaced Value"

Once all that is done… Now the fun can begin!

References

• Microsoft Power Query for Excel
• Power BI Desktop
• Getting started with Power BI Desktop
• Introduction to Microsoft Power Query for Excel
• Power Query (informally known as “M”) Formula reference