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

[Spangamamman]

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

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

 

[Rudi]

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.

 

[Bob Phillips]

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?

 

[Spangamamman]

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

 

[Rudi]

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.

View attachment 7380

Hope this helps...
(I trust I did everything the way you needed?? Hope it helps you and works for you...)

 

[Spangamamman]

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., doesn´t 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

 

[Rudi]

You can avoid the error if you choose EDIT instead of "Combine and Edit".

View attachment 7387

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

View 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

View 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.

View 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!)

 

[Spangamamman]

...



View 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.

 

[Rudi]

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?

 

[Spangamamman]

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 didn’t think of this since you didn’t mention it. Did you get the same “problem” and are just so used to delete redundant stuff that you can see don’t 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):



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

 

[Rudi]

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?

I never have a problem deleting an error...
Usually I would click on it and determine (via the error message) why it is an error and then I'll attempt to correct it.
If it is a Changing Data Type error then it can just be deleted.
Usually you will know the context of the error and you can pretty much determine if it could be deleted and recreate the step or if it is a useless error worthy of deletion.
Obviously errors at the end of the query step list can be deleted more liberally than an error at the beginning of the list, since you have many more dependent steps connected to that step. In this case it is better to fix the error than delete the step.

...if I perform everything in master data as you suggest, I end up deleting the first five rows in ONE file, but not both.

I must apologise for leading you to think that all my steps were done in the master. Let me clarify:
I imported the folder and chose EDIT
Then I covered the steps to expand the table column to produce the master query
At this point I went back to the Sample Query and expanded the table and removed the top 5 rows and some other minor clean-up
From then onwards, I stayed in the master
So please note that steps to be performed on all the files MUST be done in the Sample Query (as you correctly indicate)
I think I was just trying to over-stress the fact that you had to select the yellow folder at the master query so I lead you to think that all actions were done there.

Cheers