Forum Discussion
Get data from s3 bucket
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.
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.
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.