Load multiple CSVs from Azure Blob Storage folders

[gerritlob]

Hey,

I'm new to PowerBI and have no idea how to load my needed data tp PowerBI via PowerQuery. I hope anybody has an idea how to implement that.

As a Datasource all my files are stored on an Azure Blob Storage. These files contains Azure Cost export from a Subscription level based on Daily export for Monthly-cost data.

I have this file/folder structure:

File	File created
/subscriptioncost/subscriptionA/20220801-20220831/costexport-sdglj344j543kj5.csv	2022-08-30
/subscriptioncost/subscriptionA/20220801-20220831/costexport-sghgffgh.csv	2022-08-31
/subscriptioncost/subscriptionA/20220901-20220930/costexport-reh45trhfddfh.csv	2022-09-01
/subscriptioncost/subscriptionA/20220901-20220930/costexport-rehuz87zhg78zs.csv	2022-09-02
/subscriptioncost/subscriptionB/20220801-20220831/costexport-sdglj344j543kj5.csv	2022-08-30
/subscriptioncost/subscriptionB/20220801-20220831/costexport-sghgffgh.csv	2022-08-31
/subscriptioncost/subscriptionB/20220901-20220930/costexport-reh45trhfddfh.csv	2022-09-01
/subscriptioncost/subscriptionB/20220901-20220930/costexport-rehuz87zhg78zs.csv	2022-09-02

This is only an exaple. There are more files per Subscription based on each day of the month. I want to Import the latest file of each month and each subscription.

Has anybody an idea?

Thanks!

[mahoneypat · Accepted solution]

Here's one way to do it by splitting your filepath, grouping by subscription, and using Table.Max to get the latest record/row. Just create a blank query, open the Advanced Editor, and replace the code there with the below, to see how it works.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZDNCoMwEAbfJWfTbH7E5FhfQ7zUmERbqiSxFJ++SClUD1Ww3pblYwamKBAJwyVUvulj092rLsTZ40wYMAYSKH4fnJJpVD/7zkcctL21XIg2FfzapqcqPFCCpiUGiTmgMtlrsM4aY90STbej1QetOHyjfe1EGr0zWps5X2H4D38YZTY6m8kxLAVsgyA/vP6aYUf9/OD66/zf9csX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [File = _t, #"File created" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File", type text}, {"File created", type date}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "File", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"File.1", "File.2", "File.3", "File.4", "File.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"File.1", type text}, {"File.2", type text}, {"File.3", type text}, {"File.4", type text}, {"File.5", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"File.3", "File.4", "File.5", "File created"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"File.3", "Subscription"}, {"File.4", "DateRange"}, {"File.5", "FileName"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Subscription"}, {{"AllRows", each _, type table [Subscription=nullable text, DateRange=nullable text, FileName=nullable text, File created=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "LatestRow", each Table.Max([AllRows], "File created")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded LatestRow" = Table.ExpandRecordColumn(#"Removed Columns", "LatestRow", {"DateRange", "FileName", "File created"}, {"DateRange", "FileName", "File created"})
in
    #"Expanded LatestRow"

Pat