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

[Spangamamman]

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

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

 

[Rudi]

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.

 

[Spangamamman]

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

 

[Rudi]

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

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

 

[Bob Phillips]

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.

 

[Spangamamman]

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 it’s 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!

 

[Rudi]

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

 

[Bob Phillips]

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

 

[Spangamamman]

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 can´t 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=!ACISGXMN_czPtkg&id=8A224897002E61E8!10667&cid=8A224897002E61E8

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

 

[Bob Phillips]

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

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.

 

[Bob Phillips]

I've tidied it up and created a month number column to sort it by.

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.Identify = Table.AddColumn(filetables.Expand, "Custom", each try Number.From([Column2]) otherwise 0),
    headers.FilterOut = Table.SelectRows(headers.Identify, each ([Custom] <> null)),
    firstheader.Promote = Table.PromoteHeaders(headers.FilterOut, [PromoteAllScalars=true]),
    extraheaders.Remove = Table.SelectRows(firstheader.Promote, each ([0] <> 0)),
    identifycol.Delete = Table.RemoveColumns(extraheaders.Remove,{"0"}),
    filecol.Rename = Table.RenameColumns(identifycol.Delete,{{"Sales-Aug2014.xlsx", "Filename"}}),
    filemonth.Get = Table.AddColumn(filecol.Rename, "Custom", each Date.Month(Date.FromText("01-" & Text.Range([Filename],6,3)&"-"&Text.Range([Filename],9,4)))),
    filemonth.Sort = Table.RemoveColumns(Table.Sort(filemonth.Get,{{"Custom", Order.Ascending}}),{"Custom"})
in
    filemonth.Sort

 

[Spangamamman]

Dear Bob Phillips, thank you again for trying to help!

I was about to run it myself and compare your code with my code. Please check my specifik questions though, in my last thread. I am working the "transform sample file from binary...", ie not the default marked lowest icon in the left frame Queries, ok?

When I try to promote the first to header, I get the exclamation mark in the lowes icon below. Text saying "could not find column1". Therefore I can't continue running my example. It boils down to "where" do I do the adjustment filter rows, promote header? If I do it right, something else is wrong since I get this Column1missing problem (I understand that it is gone, though!)

Further, please elaborate what you mean by writing "and filter out further instances of same." If that is essential for running the case I do not understand you./Spangamamman

 

[Bob Phillips]

I was about to run it myself and compare your code with my code. Please check my specifik questions though, in my last thread. I am working the "transform sample file from binary...", ie not the default marked lowest icon in the left frame Queries, ok?

I don't understand what that means.

When I try to promote the first to header, I get the exclamation mark in the lowes icon below. Text saying "could not find column1". Therefore I can't continue running my example. It boils down to "where" do I do the adjustment filter rows, promote header? If I do it right, something else is wrong since I get this Column1missing problem (I understand that it is gone, though!)

I don't get that, so you must have done something different to me to get there, but I have no idea what.

Further, please elaborate what you mean by writing "and filter out further instances of same." If that is essential for running the case I do not understand you./Spangamamman

You have 4 files.
Each file has 4 rows of extraneous stuff, followed by a header row.
Then each file has real data.

So it is like this
Power Query inserted header
Sales - Aug
4 rows of unwanted stuff
Header row
n rows of data
Sales - Jul
4 rows of unwanted stuff
Header row
n rows of data
etc. etc.

You get rid of the unwanted 4 rows but you still have
Power Query inserted header
Aug Header row
Aug rows of data
Jul Header row
Jul rows of data
Sep Header row
Sep rows of data
Oct Header row
Oct rows of data
etc. etc.

Prompte the first row as header and you have
Header row
Aug rows of data
Jul Header row
Jul rows of data
Sep Header row
Sep rows of data
Oct Header row
Oct rows of data

As you can see, you still have the 3 instances of headers that need to be removed. You cannot remve them earlier when you get rid of the rubbish, you would also get rid of the AUg header so you wouldn't have a real header to promote (actually you could, if you renamed all of the columns in the header that Power Query inserts).

 

[Spangamamman]

I don't understand what that means.

I try again: It is regarding the left frame in Power Query. I insert an image below with Swedish text. The old question I referred to is the position in that box, what should me marked?
The blue * (Power Query data in Swedish). shows what is highlighted by default. Because of the default, I filtered the four rows and did all the rest with Power Query data marked. That left the four rows for three of the files - but did not generate the problem with the header.

Now I change highlightning to the one I circled, when i do all steps. Is that right? I try to filter the rows and fix the header, all with the circle marked. No complaints when I filter the rows, but the heading gives me the "missing Column1" stuff in the position marked blue *


I leave out the rest for now, but thank you for the further detail you sent me. I have not yet looked into that, I think that if I made my question clear above, we might solve the problem. I hope so! I am truly sorry for not being clear when I try to explain my problem.

 

[Rudi]

In addition to Bob's reply and the M-code he posts, here is my response.

The attached file contains the completed Excel workbook with all the steps
(To review, just point the Source steps to a folder containing the sample files)

It also contains 6 screen shots with numbered (or labeled) markers
It contains a text file that identifies each screen shot and the steps I took during the exercise to process the files to the end result.

I hope this clarifies the process for you.

View attachment 7366

Edit: Wow... I didn't see that this thread went to a pg 2! Bob has certainly been busy helping you! Anyways, if you still have questions, hopefully my screenshot documentation will further assist!

 

[Bob Phillips]

I try again: It is regarding the left frame in Power Query. I insert an image below with Swedish text. The old question I referred to is the position in that box, what should me marked?
The blue * (Power Query data in Swedish). shows what is highlighted by default. Because of the default, I filtered the four rows and did all the rest with Power Query data marked. That left the four rows for three of the files - but did not generate the problem with the header.

Now I change highlightning to the one I circled, when i do all steps. Is that right? I try to filter the rows and fix the header, all with the circle marked. No complaints when I filter the rows, but the heading gives me the "missing Column1" stuff in the position marked blue *

View attachment 7365

You should be working with the query that the arrow is pointing at. The other one is the sample file that the main query, the arrow is pointing at, references.

 

[Spangamamman]

Dear Rudy and Bob Phillips, I'm through, I run the files!

How can I thank you both? THANK YOU! I new world of wonders is opening... I am certain that the right move in refreshing Excel on ny part, was to dig into Power Query, rather than refreshing VBA, having to make ha choice.

Now, my only concern is making this excellent help more accessible to others? The thread is long. I want to share my conclusion, there are a few highlights. Any other reader, you may just read this last comment. If not complete, please follow all excellent thoughts above, especially Rudys last comment, with a step by step instructions with comments.


Beginners like me, please notice:

1. Beware of your choice if file/icon to the right, when the query window opens. When running Rudy's examble, I do all my changes in the Transform sample...

2. When completely done, I clicked the bottom "Master Query"

3. Important here - When you run inte error there, you are still right and apparently this is common after transformations in the sample. You will understand, as I did, that yes, you have deleted the namne "Column1". The way of solving the case is super easy - Simply delete the step "changed type" in the master query.

4. Close and load... BUT if you follow the answers above, you will get more in dept knowledge.

My greatest hurray and thank you to all of you out there who help out just like Rudy and Phillips helped me now. In this way, I don't feel alone (plus that I get the stuff done...) Great hugs from Sweden! /Spangamamman



PS I am humble that there may be other ways to run this example than in this short highlight. But to me, this is most logical way of doing it.

 

[Bob Phillips]

Dear Rudy and Bob Phillips, I'm through, I run the files!

How can I thank you both? THANK YOU! I new world of wonders is opening... I am certain that the right move in refreshing Excel on ny part, was to dig into Power Query, rather than refreshing VBA, having to make ha choice.

It has been a pleasure mate. I think I am probably speaking for Rudi as well as myself that we think Power Query is the jewel in the Power BI crown, and seeing and helping other people turn on to it is a joy.

Now, my only concern is making this excellent help more accessible to others? The thread is long. I want to share my conclusion, there are a few highlights. Any other reader, you may just read this last comment. If not complete, please follow all excellent thoughts above, especially Rudys last comment, with a step by step instructions with comments.

Maybe, but remember that this difficulty was mainly because a) you were just starting, and b) you were combining files in a folder which has a few more components. Most power Query transforms will be a lot simpler to get started. Of course, they can get much more complex depending on the state of the data at the start and where you want to go with it, but it is usually simple to start.

1. Beware of your choice if file/icon to the right, when the query window opens. When running Rudy's examble, I do all my changes in the Transform sample...

As above, this applies in this instance., not always.

Master Query"

Again, not always.

3. Important here - When you run inte error there, you are still right and apparently this is common after transformations in the sample. You will understand, as I did, that yes, you have deleted the namne "Column1". The way of solving the case is super easy - Simply delete the step "changed type" in the master query.

Not sure I follow all of that, but I do follow the bit about Changed Type. I nearly ALWAYS delete that step, and change the types I want changed when I want them changed.

4. Close and load... BUT if you follow the answers above, you will get more in dept knowledge.

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

My greatest hurray and thank you to all of you out there who help out just like Rudy and Phillips helped me now.

In this way, I don't feel alone (plus that I get the stuff done...) Great hugs from Sweden! Spangamamman

You are not alone. I am here, Rudi is here, Marcel is here, and Ken even deigns to grace us with his company occasionally .

 

[Spangamamman]

Thanks again! /Spangamamman

 

[Rudi]

...I think I am probably speaking for Rudi as well as myself that we think Power Query is the jewel in the Power BI crown, and seeing and helping other people turn on to it is a joy...

I can confirm your quote Bob!
- PQ is a jewel in the BI crown, and,
- it's always a pleasure to lend assistance where possible on these forums.

Glad to hear you succeeded Spangamamman. Persistence and patience always have a reward!

TX to you too Bob for your valued input and assistance.

Cheers