Forum Discussion

gerritlob's avatar
gerritlob
Regular Visitor
3 years ago
Solved

Load multiple CSVs from Azure Blob Storage folders

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:

FileFile created
/subscriptioncost/subscriptionA/20220801-20220831/costexport-sdglj344j543kj5.csv2022-08-30
/subscriptioncost/subscriptionA/20220801-20220831/costexport-sghgffgh.csv2022-08-31
/subscriptioncost/subscriptionA/20220901-20220930/costexport-reh45trhfddfh.csv2022-09-01
/subscriptioncost/subscriptionA/20220901-20220930/costexport-rehuz87zhg78zs.csv2022-09-02
  
/subscriptioncost/subscriptionB/20220801-20220831/costexport-sdglj344j543kj5.csv2022-08-30
/subscriptioncost/subscriptionB/20220801-20220831/costexport-sghgffgh.csv2022-08-31
/subscriptioncost/subscriptionB/20220901-20220930/costexport-reh45trhfddfh.csv2022-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!

Need Help
  • mahoneypat's avatar
    mahoneypat
    3 years ago

    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

     

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    3 years ago

    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