Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
10 | |
10 | |
8 | |
7 |