Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 38

Thread: Help running great example "Combine Multiple Excel workbooks in Power Query"

  1. #1
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    13
    Articles
    0

    Help running great example "Combine Multiple Excel workbooks in Power Query"



    Register for a FREE account, and/
    or Log in to avoid these ads!

    This is a link on this site:
    https://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

    First, I work in Excel 2016 through Office 365, ie I don´t have Power BI. This causes some general frustration since what I find searching on the net, but I believe that I should be able to run the complete example still.

    It is great tutorial for a beginner like me, but I can't run it all through.

    "Step 2, Convert into functions" turns up like this. I am supposed to exchange the hard codet file path with "filepath". My result is just like the example BUT, see the red text at the end :

    Källa = Excel.Workbook(File.Contents("C:\Users\thoma\Documents\Kajsa\Övning excel\Power Query data\Sales-Jul2014.xlsx"), null, true),

    Is it possible to describe why I get that and what I should do? I have tried different ways o solve it, but at the end, I get error in step 3:

    Go to Add Column –> Add Custom ColumnEnter the following: fnGetContents([Folder Path]&[Name])
    Click Enter
    Right click the new “Custom” column –> Remove Other Columns

    As it is, since I don´t have Power Pivot I can´t follow the exact text above. None the less, I can´t run "the magic" and combine all the data.

    Under Ad column I klick -my translation - call for designed functions. I find the function/question, and have tried to run it in different ways. I can choose "filepath" and a drop down of choices. I don´t understand the choices and you tutorial does not cover my challenge. Any attempt gives the new column just "error" in the cells below.

    Is there a way to help me? As I said, I believe the problem is in the code above. Thank you very much in advance, if someone looks into this!

    Kindly, Spangamannan

  2. #2
    Acolyte Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    47
    Articles
    0
    Ken wrote that article in February 2015. A lot has changed since this article; in particular a new dynamic (or automatic) way to import multiple files of any type. If you have the latest version of PQ, you can simply choose the "From Folder" option and navigate to the folder that contains the Excel files to process. PQ will import all the content and allow you to adapt the First File in the folder to structure a pattern to follow for the rest of the import. The whole process is completely automatic and you will not need to adapt the M-Code according to Kens article.

    For a newer article about this new "Combine Binaries" precess, see here... (great article BTW, Ken!)

    Of course the process to make a manual function does still apply if you need the flexibility. The auto way is just more convenient and high adaptive anyways, so not really a need to go the manual route.
    Regards,
    Rudi
    www.eileenslounge.com

  3. #3
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    13
    Articles
    0

    Question Confusing regarding Excel 2016 limitation within O365 - the advice might not work?

    Thank you, Rudy for trying to help me!

    Yes, it is true that the article is old. However, you write: "If you have the latest version of PQ, you can simply choose the "From Folder" option and navigate to the folder that contains the Excel files to process. PQ will import all the content and allow you to adapt the First File in the folder to structure a pattern to follow for the rest of the import."

    I have a 10 day old Office 365 Personal, which includes - only parts of? - Power Query? However I don't seem to be able to follow your advice. I start a new file, chose from folder and get a list with the following headings: "Content; Name; Extension; Date accessed etc".

    Please guide me further how I design a query (The one that deletes the first 4 rows, change data-type etc) and apply it to the files I am supposed to combine in the example. My version om O365 gives me the following options as buttons to klick, facing the four files:

    My buttons of choice (my translation). All buttons have a few alternatives: Combine // Download // Edit // interrupt

    Am I OK to continue from this "entrance", to go further, design my query and combine the four files? I just can't make it! I can combine, but then they are combined without the query and the whole idéa of smart function is lost.



    PS Unfortuantely I didn't buy directly from Microsoft and the reseller won't refund my O365 so I can buy a complete single Excel 2016. Is there a way to find out what tutorials that do cover the excel 2016 that I bought? (I truly asked the sales personal at livecard that sold me this, that all I wanted was a complete excel and noting else. I guess the problem is the way Microsoft separates power BI as something to charge extra. i.e., the salesperson was right in a way. For me, it is so hard finding out what I have bought that I can use, and what I do not have... I am preparing for a new position at a new employer, which will include a lot of excel. I am not up to date since versions like 2007...

  4. #4
    Acolyte Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    47
    Articles
    0
    Hi,

    Can I ask if you reviewed the link I posted in my reply above. Ken has created a very good step-by-step article on this combining binaries process. It wuill guide you through the process and explain all the changes (improvements) to the new multiple file import feature of Power Query. If you have O365, I'm pretty sure that you will have the updated version of PQ and this process will apply to you.

    Try the link out and use the article to work your way through the process to import multiple excel files. It is not hard to do and the explanations are very clear if you take it step-by-step.

    Start at this point in the article...

    Click image for larger version. 

Name:	Image 027.jpg 
Views:	10 
Size:	51.9 KB 
ID:	7359

    Please note that I'm not trying to be lazy in helping you. I'm taking this route because it is the best place to start helping you. I could give you a full step-by-step in this thread, but ultimately its going to boil down to the same advice Ken provides. If you have individual questions on the process if you don't understand something, feel free to post your question, but I'd encourage you to try the article first.
    Regards,
    Rudi
    www.eileenslounge.com

  5. #5
    Quote Originally Posted by Spangamamman View Post

    I have a 10 day old Office 365 Personal, which includes - only parts of? - Power Query? However I don't seem to be able to follow your advice. I start a new file, chose from folder and get a list with the following headings: "Content; Name; Extension; Date accessed etc".

    Please guide me further how I design a query (The one that deletes the first 4 rows, change data-type etc) and apply it to the files I am supposed to combine in the example. My version om O365 gives me the following options as buttons to klick, facing the four files:

    My buttons of choice (my translation). All buttons have a few alternatives: Combine // Download // Edit // interrupt

    Am I OK to continue from this "entrance", to go further, design my query and combine the four files? I just can't make it! I can combine, but then they are combined without the query and the whole idéa of smart function is lost.
    That is fine, I just tried it and that is what I get.

    The combine button has a dropdown arrow, click that and select the Combine & Edit option. You are then good to go.

  6. #6
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    13
    Articles
    0

    Question

    Thank you both very much. We are getting there I'm so thrilled getting the chance to write MAGIC, all set, but however, there are still problems.

    1. I get the start, using the advice "Combine and edit" works. Good so far.

    2, Not important I guess, but I choose file July as the sample file (just because its the first month), but the editor still shows/chooses August (alphabetical). Comment?

    3. Then please confirm that the current adjustment (delete first 4 rows etc) should be done with the default marking "Transform Sample Binary from Combine Binaries" to the left, OK? My present problem - I used the button to deleted the first four rows, but ended up having the four rows left in the remaining three files, in the new excel sheet after clicking close and load. What mistake do I make? I have tried over and over.

    Thank you for your patience in helping me!

  7. #7
    Acolyte Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    47
    Articles
    0
    #2 - You can choose any file from the list of files you are importing, but to be accurate, the most ideal file to choose is the one that most closely represents the context for the actions (or steps) you want to apply for the rest of the files. This file is the representative for all the other files and the actions you perform on it must be able to be performed on the others successfully for the whole query append process to work well. The current PQ versions allow you to select a file, but it has a default setting for "First File" which automatically extracts the first file from the folder to use as a sample file.

    #3 - The "Transform Sample ......" is the correct item to use. (This is the sample file out of the files in the folder to which you apply the steps and these steps are then applied to all the files. The combined files query is the last item in the list on the left; the one listed in the folder called: "Other Queries".

    If you import a folder of *.csv files (for example), the name of the sample will be "Transform Sample Binary from ... "
    If you import a folder of *.xlsx files, the name of the sample will be "Transform Sample File from ... "

    If you have removed four rows from the top of your sample file, it will have applied this action (or step) in your other files. This is how the query works. Are you sure that the other files have the same structure? Maybe they had 8 rows at the top and after you removed the 4 in the sample, the others still have four as they originally had 8 rows?????

    If you can supply us with a few desensitized samples of your files in a zipped attachment, I'll be happy to check what the issue is and help create a query that represents the steps you could follow. Maybe this can further help you understand the process.

    BTW: Here is another very good video where Miguel explains in detail all the files or queries that are created in the left panel and what their purpose is. It is explained in the Power BI Desktop application, but this will be exactly the same context in Power Query. I trust it will further clarify the reason for all the items in the left panel.
    Regards,
    Rudi
    www.eileenslounge.com

  8. #8
    Quote Originally Posted by Rudi View Post
    #2 - You can choose any file from the list of files you are importing, but to be accurate, the most ideal file to choose is the one that most closely represents the context for the actions (or steps) you want to apply for the rest of the files. This file is the representative for all the other files and the actions you perform on it must be able to be performed on the others successfully for the whole query append process to work well. The current PQ versions allow you to select a file, but it has a default setting for "First File" which automatically extracts the first file from the folder to use as a sample file.
    Be aware that the reason that they added the First File option was to manage the situation where the files in the folder might change. So one day, you might select the April file as your sample file, but some months later, if that file is deleted then your query will fail. Select First File as your sample file, and this failure will not happen.

    I will also try any files you post, so give us a shot .

  9. #9
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    13
    Articles
    0

    Question

    Thank you again!!! I am truly learning a lot from both what you write and your good links. Hopefully your thoughts and links to great educational files also will help others. Still, the tutorial is not working all the way.


    1. Please confirm again with complete name, where I should perform my changes to the sample file. I made a mistake above where I wrote the default partly my translation - Transform file from Combine Binaries. The default marker is on the last icon, my translation Other queries / Combine Binaries. I should change this to the above stated and perform all my changes for this simple task here, OK? That seems logical and follows what you wrote above / the youtube you gave me.
    2. I have mostly done my trial and errors from the default setting, ie that has been the major problem, right? Following #2, I do manage to delete the four rows in all four files. Thumbs up! BUT I cannot klick use first row as column name here. This gives med the error message in box Combine binaries that it cant find Column1


    3. As it comes to your so nice offer to check the files, yes pleas do. I have downloaded them from this site, this is the link: https://onedrive.live.com/?authkey=%...224897002E61E8

    Please trust that I study the files you have linked, so I learn a lot. I will follow them again. I still need guidance to solve this example and I really trust that the solution will be helpful in real life later. Kindly/Spangamamman

  10. #10
    I pulled in all of the files, combined them using first file as sample, selected sheet1, and expanded them all. Each has some leading rows to remove, so I filtered them out, promoted the first to header and filter out further instances of same.

    This is the M code I generated

    Code:
    let
        Source = Folder.Files("C:\Users\Bob\Downloads\Spangamamman"),
        files.Transform = Table.AddColumn(Source, "Transform File from Spangamamman", each #"Transform File from Spangamamman"([Content])),
        filedata.Remove = Table.SelectColumns(files.Transform, {"Name", "Transform File from Spangamamman"}),
        filetables.Expand = Table.ExpandTableColumn(filedata.Remove, "Transform File from Spangamamman", Table.ColumnNames(#"Transform File from Spangamamman"(#"Sample File"))),
        headers.FilterOut = Table.SelectRows(filetables.Expand, each ([Column1] <> null and [Column1] <> "Fluffy Bunny Pet Store" and [Column1] <> "For the month ended August 31, 2014" and [Column1] <> "For the month ended July 31, 2014" and [Column1] <> "For the month ended October 31, 2014" and [Column1] <> "For the month ended September 30, 2014" and [Column1] <> "Sales Listing")),
        row1.ToHeader = Table.PromoteHeaders(headers.FilterOut, [PromoteAllScalars=true]),
        extraheaders.Remove = Table.SelectRows(row1.ToHeader, each ([SalesPersonID] <> "SalesPersonID"))
    in
        extraheaders.Remove
    You will have to change the folder path.
    Last edited by Bob Phillips; 2017-09-14 at 04:39 PM.

Page 1 of 4 1 2 3 ... LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •