zipped file - how to unzip in power query? more explanations needed from previous post

[elaine123]

Hi,

I have a query that I am hoping someone can answer or provide more detail from previous posts (newer to power query than most on here) so will probably need a more detailed explanation or more details on how to implement. 

1) I tried the method from Reading Zip files in PowerQuery / M (sql10.blogspot.com) and from curbal.  I get an error with this...

2) I suspect I had the same problems as this poster Solved: Failure to extract file from ZIP - Microsoft Power BI Community which was resolved.  My files are also coming from SAP as mentioned in this message on the community. 

3) I tried following through the to the answers posted on this to rest api ? but I think this is where I may have got lost come unstuck?  
can someone walk me through the answer I should use? as the poster messaged in 1) solved this and exactly where in power query I should post this code etc?

[tackytechtom]

Hi @elaine123 ,

I tested it with the query posted by lbendlin and it worked...

1) add a blank query:

2) click on advanced editor:

3) paste in the first code:

4) Rename the query to unzip:

5) add a new blank query

6) post the second script into the advanced editor of the new query and substitute the URL:

7) done

Does this work for you? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[elaine123]

Hey @tackytechtom thank you so much for all your help....  some good news that has worked... but I'd like to be able to unzip a folder with lot's of zipped files at once... hence why I had already tried the method in the video that you sent me...  and I think the way my files are zipped as they SAP Business objects outputs means that method didn't work. 

your working solution I assume has to point to one zipped file... ? and we can't point it to a folder?

whereas I would like a solution that can unzip all files in a folder at once, because I would like to merge them into one table, and to not have to do lots of seperate querys if that makes sense?

I have one folder with a number of seperate zip files, hence why I tried the method in the video you posted earlier? any thoughts ideas? I AM SO apprecative of your time to reply and answer 😁   I wonder if you are able to make sense of the replys @artemus  reply which the original post they had solved?Solved: Failure to extract file from ZIP - Microsoft Power BI

[tackytechtom]

Hi @elaine123 ,

Have you already tried it as explaned here?

Let me know 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[elaine123]

Hi Thanks for your swift response. I tried this method in this video and I get the following error which also came up in this post  Solved: Failure to extract file from ZIP - Microsoft Power BI

I am looking for more of an explanation to the solved answer in this  link Solved: Failure to extract file from ZIP - Microsoft Power BI

I tried to follow the threads but got confused and regarding the API etc

[tackytechtom · Accepted solution]

Hi  @elaine123 ,

Use the script from artemus to create a new function (‎01-09-2020 06:57 PM):

Solved: Re: How to connect Azure DevOps REST API in to pow... - Microsoft Power BI Community

Then add the following line between let and ushort 

ZIPFile = Binary.Buffer(File.Contents(ZIPFile)), 

so it should look like this:

Add a new query with the following code:

You should then end up with multiple lines each representing one of the files:

From there it is "just" using M and Power Query to unfold all these records and union them. I can imagine the code can be a bit of a pain if there is a dynamic number of files meaning one day there are 3 and another day there are 7 files. But this is probably a topic of another thread 🙂

Let me know if this solves your issue!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom

[elaine123]

Thank you ! much appreciated.