Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 30 of 31

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

  1. #21
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    19
    Articles
    0
    Excel Version
    Microsoft Office 365 Business

    Question Testfile solved - very similar real life issue doesn't work, see enclosure



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

    Hu guys, bad luck...

    1. I went head on with a live example, see enclosed files. I add the problem this to this tread since it a practice test of exactly what we have worked with. Maybe it is another common issue that you can highlight here? Believe me, I have checked the net for answers, but I get no hits when I search for the error message in Swedish (I don't know the English version of the error message).


    As you will find, the master Query will protest saying that the - my translation - "key doesn't match the rows in the column". Below it also states this, which I don't understand: Key= Record ; Table=Table. Do you find the problem?

    2. Since the thread is still alive, I do add this question to: You´re into something really important when you write a comment regarding clicking "close and load", Bob: "Be aware, load puts the results back in an Excel worksheet. You don't have to do that, you could just add to the datamodel. Why would you want to do that? Well you might have a few queries, load them into the datamodel, and the manipulate all of those resultant tables in Power Pivot, or regular pivots (assuming you have Excel 2013 or above)."

    You are updated with the fact that I am currently only running O365 Personal, ie no access to complete Power BI. If it is not too much trouble, can you explain my present limitations? What is included in the term "Datamodel"? Is the power Query "set-up" we have worked within the framework of "question editor? I can't "see" this query editor again, unless I close and load my excel file if I am not mistaken. Then I can access it through the button "Questions and Connections". Am i supposed to be able to save the question editor, and that is then my Datamodel?

    Kindly, wishing you a great week end! /Spangamamman
    Attached Files Attached Files

  2. #22
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    Hi,

    I cannot replicate any issues that you claim to have on the sample files you attached. If I open these in PQ, I can clean up and restructure the data, performing all manner of steps on it without any errors or issues.

    With regards to your question on "What is the Data Model";

    A brief layman's clarification is this:

    It is a reserved memory reservoir housed within the workbook that allows one to upload/store data. Power Pivot uses it for data storage and for relationships and DAX calculations, but PQ can take advantage of it to extract LARGE volumes of data from external sources, do cleansing transformations on it and then upload it into the data model. The model is a memory reservoir, so it can store many millions of records (superseding the limit of a worksheet). The benefit of the data model is that it stores the data in a highly compressed manner, breaking up the data into non redundant tables/lists. The data uploaded into the model can be accessed by pivot tables and can therefore take advantage of summarizing and aggregating large volumes of data.

    There is no need to use the data model unless you need to store the data in the workbook for "transportation" means, or if you have several data sources and need to create relationships between the data or to build more flexible aggregated formulas called DAX measures that are not available in pivots not linked to the data model.
    Regards,
    Rudi
    www.eileenslounge.com

  3. #23
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,706
    Articles
    0
    Excel Version
    O365
    I get an error when I try to combine these two files. The first has a worksheet name kontotransactionlist, the second is called kontotransactionlist(1)> The transform is based upon the first name, which it does not find in the secnd file, and so errors. Is this the error that you mean?

  4. #24
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    19
    Articles
    0
    Excel Version
    Microsoft Office 365 Business

    Question

    Quote Originally Posted by Bob Phillips View Post
    I get an error when I try to combine these two files. The first has a worksheet name kontotransactionlist, the second is called kontotransactionlist(1)> The transform is based upon the first name, which it does not find in the secnd file, and so errors. Is this the error that you mean?
    1. OK, I believe you describe the namn of the work sheets. I thought that that doesn't matter though, does it?

    2. I am so grateful to your attention, so I did try to rename the sheets. I gave them both the same namn and ran the editor again. My problem remains, I might have renamed the wrong thing? Please see images where the function in master Query shows error. I have translated the message in my previous Quote.

    Further - it's week end! I am all "wrapt up" in this, but take your time, I just want to pass on my reply when I saw your answer. All the best/Spangamamman
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture2.JPG 
Views:	12 
Size:	54.8 KB 
ID:	7378   Click image for larger version. 

Name:	Capture3.JPG 
Views:	11 
Size:	31.6 KB 
ID:	7379  

  5. #25
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    Attached is the workbook containing the queries for the imported tables.
    According to the steps I took, all worked well and no errors and no renaming was needed.

    I took the two sample files and placed them into a folder celled "DATA" on the C:\ drive.
    (So the sample files had the path: C:\DATA\export HB Testfil.xlsx and C:\DATA\export HB Testfil add.xlsx )

    Then I opened a blank new workbook and chose "From Folder" from the PQ Import menus.
    I browsed to the DATA folder on the C:\ drive

    The rest of the steps are documented in the APPLIED STEPS in the queries in this attached workbook.

    Attachment 7380

    Hope this helps...
    (I trust I did everything the way you needed?? Hope it helps you and works for you...)
    Regards,
    Rudi
    www.eileenslounge.com

  6. #26
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    19
    Articles
    0
    Excel Version
    Microsoft Office 365 Business
    Thank you both again. Very helpful to be able to follow and example as well, great thanks. However, I have tried and check and tried. I thought I found a step to solve it. Still not.


    1. I can’t load the file namned export HB testfil. The function in the master query DATA shows error, see capture4. Ie I get an immediate problem after klick combine and edit (at first I didn’t realize that the problem came from start. You have helped me to “read” the query manager). It has something with what Bob writes regarding banktransactionlist? I just can’t figure out that, however.

    Please note the error. I did try to run it the way you did, Rudy, by deleting all column but “data” in the example data file, and then expand.
    I am very sorry for not getting further. I let you know anyhow, maybe someone finds out what it is? I have also tried to eliminate so both files have only one sheet etc., doesnt work.


    1. Detail Rudy, it seems like you didn't remove any other “error” than a manually deleted “can’t find column1” after using the first row as header, is that right?


    Kindly, Spangamamman

    Click image for larger version. 

Name:	Capture4.JPG 
Views:	10 
Size:	58.3 KB 
ID:	7383

  7. #27
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    You can avoid the error if you choose EDIT instead of "Combine and Edit".

    Attachment 7387

    Once the query loads, remove the other columns (which are not necessary)

    Attachment 7388

    Then click on the double down pointing button (to expand the tables), and then (IMPORTANT - what I do... is to select the yellow folder ("Sample File Parameter1") and NOT the kontotransactionlist sheet. Selecting the folder allows you to further edit/filter the sheets in the files. If you only select the sheet, then the query expects all files to have this sheet.

    Select the folder and choose OK

    Attachment 7385


    PQ will expand the output and provide you a list of all sheets in all files. At this point you should see that there is no error on the tables (hopefully! - I have never got an error using this process!)
    If you want delete the columns you don't need, and then expand the DATA column.

    Attachment 7386

    After this, I will clean up the query as required....
    You can use the sample workbook I uploaded in my previous reply to review the cleanup (if you need it!)
    Regards,
    Rudi
    www.eileenslounge.com

  8. #28
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    19
    Articles
    0
    Excel Version
    Microsoft Office 365 Business
    Quote Originally Posted by Rudi View Post
    ...



    Attachment 7386

    After this, I will clean up the query as required....
    You can use the sample workbook I uploaded in my previous reply to review the cleanup (if you need it!)
    Dear Rudy, I hope is not too time consuming posting the follow ups. I was so glad this time, thought we solvedit, but there is another issue and I really hope you still have a thought:

    Please see my enclosure. What works:
    I can see the two sheets in the master Query. Nice.

    Then I move up to the example file to do the (optionally) delete excess columns and expand data. Note however, that I can’t mark the folder as you do in your image, I must mark the symbol of the spreadsheet. Can that matter now as well?

    Anyhow, I run into a missing match. My trial and error includes deleting and not deleting other columns.
    Click image for larger version. 

Name:	Capture5.JPG 
Views:	11 
Size:	52.3 KB 
ID:	7390

  9. #29
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    Image #3 in my previous post was done on the master query, not on the Transform Sample Query.
    As a matter of fact, I never worked on the Transform Sample Query during the whole process as there is hardly any significant transformations to do.
    When I selected the Folder (#3 image) it was while I had the master query selected.

    The error in your last screenshot: Just delete the Change Types step (the last step) --does that help?
    Regards,
    Rudi
    www.eileenslounge.com

  10. #30
    Seeker Spangamamman's Avatar
    Join Date
    Sep 2017
    Location
    Sweden
    Posts
    19
    Articles
    0
    Excel Version
    Microsoft Office 365 Business

    Red face

    Quote Originally Posted by Rudi View Post
    Image #3 in my previous post was done on the master query, not on the Transform Sample Query.
    As a matter of fact, I never worked on the Transform Sample Query during the whole process as there is hardly any significant transformations to do.
    When I selected the Folder (#3 image) it was while I had the master query selected.

    The error in your last screenshot: Just delete the Change Types step (the last step) --does that help?
    Thank you Rudy, this time we are in one way definitely through!!!

    1. Doing this, it all works! THANK YOU! I didnt think of this since you didnt mention it. Did you get the same problem and are just so used to delete redundant stuff that you can see dont affect the results, that you just forgot to mention it? It turns up when i expand data (Text translated: column Data could not be found in the table):

    Click image for larger version. 

Name:	Capture7ExpandDataGivesErrorIDelete.JPG 
Views:	12 
Size:	54.1 KB 
ID:	7393

    Is there a way to explain when it is safe just deleting some errors that occur, like the one you describe 😊? I guess that comes with experience mostly, right?


    2. Extra - Working on master data - thing is, if I perform everything in master data as you suggest, I end up deleting the first five rows in ONE file, but not both. Do you have a suggestion how that could be? Since I am more comfortable doing the work in Transform Sample Query, after having been your student in this thread, this is extra, we can call it done.

    To be honest -by now, I feel perfectly comfortable with the great portrait the site has given me!!! It couldn't have been a better match in many occasions... But now I am so pleased. Thank you again! /Spangamamman

Page 3 of 4 FirstFirst 1 2 3 4 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
  •