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
ResultDailyPosition 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"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//== 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