Get data from s3 bucket

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.