Merging multiple Excel files in a folder into columns, not rows

[SteynBS]

Hi Guys,

I am using Power Query to merge multiple Excel files in 1 folder, but instead of throwing the data below the other in rows, I need it in columns.

So I have a master file that I will use as a seperate source called Master. The layout looks like this

I will then have multiple other excel files in a folder they will have different names, but they will all have the same layout:

What I need th achieve is all the files in the folder needs to merge with the Master file using the "item" as the join. The final product will look like this:

Currently what I am doing is using each file as a source and merging it to the Master file using the "item' as the join. Is there anyway I can achieve this in a faster way?

 

[SteynBS]

You're welcome.
Glad to hear it helps.

I am now just struggling with aligning the data. The Master File has 60k entries, Site A203 Has a total of 27k. With a manual XLOOKUP formula, I match 21k articles, but through this, I match only 8200 entries. I thought it could be the join and played around with it, but no luck. I am trying to figure out why it would not bring over all the matched entries for the Site to the Master.

 

[smozgur]

I would look for a non-matching entry and compare the join IDs in the Master and the Site file. Maybe they don't match due to a space character?

 

[SteynBS]

I would look for a non-matching entry and compare the join IDs in the Master and the Site file. Maybe they don't match due to a space character?

I have cleaned up all the data entries. I will figure it out. Thank you again, sir.

In the future, I would like to import the data directly from the source SAP HANA connector. Just for interest sake, would what you have done for me today be possible if the source is from a calc view in SAP Hana?

 

[smozgur]

Just for interest sake, would what you have done for me today be possible if the source is from a calc view in SAP Hana?

I don't know about SAP Hana connector. But, when you get there, just post a new question about it. And if there is a relation with this thread, make sure you put a reference to this page in the new question. I am sure there are others who knows about it. It is just a data source that you'll get the data from instead of workbooks.

 

[SteynBS]

I don't know about SAP Hana connector. But, when you get there, just post a new question about it. And if there is a relation with this thread, make sure you put a reference to this page in the new question. I am sure there are others who knows about it. It is just a data source that you'll get the data from instead of workbooks.

Thank you, may you be abundantly blessed for you kindness and assistance

 

[smozgur]

Thank you, may you be abundantly blessed for you kindness and assistance

You're welcome.
Thanks for the interesting question which provided me with an opportunity to showcase the usage of the List.Accumulate() function in M.