none
How to loop function that unzips for multiple files with the same name but different subdirector? RRS feed

  • Question

  • I get daily data extracts from AWS sent to a folder repository. I want to aggregate them in Power BI for analysis over time, but to do that I need some help with writing the correct M code to work through a little snag.

    While each daily file is named the same, their sub-directories are different

    The folder paths are: automatedReportExtracts\AWS_Usage_Cost_gzip\YYYMMDD-YYYYMMDD\DAILYrandomString

    I can't control the three sub-directories. That's how AWS generates the reports I automatically pull from their s3 bucket into my automatedReportExtracts.

    Normally, this wouldn't be a problem because the file names are the same and I could write a little M to expand and append the content for each file. BUT the files come as gzips (again, AWS controls this, not me).

    I have written a simple function that can unzip an individual file. Here's the code:

    (ZIPFile) => 
    let
        Unzip = Binary.Decompress(
            File.Contents(ZIPFile),Compression.GZip)
    in
        Unzip

    But how do I tell this function to iterate through each the files in the table I have above? I need to unzip all of them before I expand and append the content...

    Friday, February 10, 2017 6:08 PM

Answers

All replies

  • "The folder paths are: automatedReportExtracts\AWS_Usage_Cost_gzip\YYYMMDD-YYYYMMDD\DAILYrandomString"

    That looks like one path to me.

    "But how do I tell this function to iterate through each the files in the table I have above?"

    Which table are you referring to?

    Friday, February 10, 2017 8:54 PM
  • Hi Jack. If you do "From Folder" in Power Query, it will implicitly include files from all subdirectories. Can you just point PBIDesktop at your automatedReportExtracts folder, then run your function over each of the files using "Invoke Custom Function..."?

    Ehren

    Monday, February 13, 2017 8:43 PM
    Owner
  • Hi Jack,

    if your issue has been solved with any of the above suggestions, please mark them as answer. If not, please give us more details referring to the questions raised. Thx.


    Imke Feldmann TheBIccountant.com

    Wednesday, February 15, 2017 9:14 AM
    Moderator