Get data from s3 bucket

[Anonymous]

Hello everybody,

 I want ot know please how can I get data from a bucket S3 AWS? 

Thank you.

Best,

[PhilipTreacy]

Hi @Anonymous 

Do you want to retrieve a specific file? Or get a list of files?  Or load data from several files?

If the bucket is set to Public access you can get a list of files using HTTP (Web Connector).  S3 returns an XML file listing all files in that bucket.  Copy/paste this code into a new blank query and replace BUCKETNAME with your bucket name.

let
    Source = Xml.Tables(Web.Contents("https://BUCKETNAME.s3.amazonaws.com/")),
    #"Expanded Contents" = Table.ExpandTableColumn(Source, "Contents", {"Key", "LastModified", "ETag", "Size", "StorageClass"}, {"Contents.Key", "Contents.LastModified", "Contents.ETag", "Contents.Size", "Contents.StorageClass"})
in
    #"Expanded Contents"

Phil

---

If I answered your question please mark my post as the solution.

If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Hello @PhilipTreacy ,

Thank you for your answe. All my data stored in S3 in json format on diferent folder.

I want to have access with Power BI to those files.

What you propose to me is not work.

[PhilipTreacy]

Hi @Anonymous 

If your data is not sensitive and doesn't require securing, you need to have the bucket set up so that all files and folders are public access.  You can then get an XML file showing all files in that bucket e.g. try this link https://mothpbiforum.s3.amazonaws.com/

If I answered your question please mark my post as the solution. If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Load that in PBI using Xml.Tables(Web.Contents("https://mothpbiforum.s3.amazonaws.com/")) and transform the XML to get the list of files.  See this PBI file that contains this query

let
    Source = Xml.Tables(Web.Contents("https://mothpbiforum.s3.amazonaws.com/")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"MaxKeys", Int64.Type}, {"IsTruncated", type logical}}),
    #"Expanded Contents" = Table.ExpandTableColumn(#"Changed Type", "Contents", {"Key", "LastModified", "ETag", "Size", "StorageClass"}, {"Key", "LastModified", "ETag", "Size", "StorageClass"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Contents", each Text.Contains([Key], ".json")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "JSON", each Json.Document(Web.Contents("https://" & [Name] & ".s3.amazonaws.com/" & [Key]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Name", "Prefix", "Marker", "MaxKeys", "IsTruncated", "LastModified", "ETag", "Size", "StorageClass"})
in
    #"Removed Columns"

 You can then access the JSON data in the files.

If your files are not publicly available you will need to use the Amazon S3 REST API and authenticate your requests to S3.

Phil

---

If I answered your question please mark my post as the solution.

If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.