Tried to do this in Access and it didn't work out. Maybe Excel can?

[kmprice710]

I have five tables: car sales, motorcycle sales, jet ski sales, trailer sales, and boat sales.
Each table has different columns but they have some column elements: customer ID #, company name, customer ID #, customer name, year.
Each row has the entire year's purchases by a customer but not every customer has a purchase row on each table.

I'd like to have 1 table that has 1 row per customer per year with all of their purchases, regardless of car, boat, jet ski, trailer and motorcycle.

How can I make that table? If the same customers were on each sheet and each of them had 1 row in each sheet, it would be a simple sort. But it won't work with this.

Any ideas?

 

[kmprice710]

See, I do not have all of those steps when I load the xls file provided here.

did you see on the picture which query is selected?

Yep, when I click on boats, I don't have those steps.

 

[sandy666]

with single click

 

[kmprice710]

with single click

When I single click on them I see the same three things under applied steps and they are not what are in the image provided unfortunately. How many steps did what you do involve and how long did it take?

 

[sandy666]

update your profile ( Account details ) about Excel version and OS, don't forget to scroll down and save

How many steps did what you do involve and how long did it take?

as many steps as there are in each query, time depends on your skills and the quality of the file

maybe try double click

 

[kmprice710]

as many steps as there are in each query, time depends on your skills and the quality of the file

maybe try double click

Double clicking just allows you to change the name of the Query.

I still have to transform the other 100,000+ rows and although you know how to do it, I have no idea how to do it and really am no closer to figuring it out.

When I open those CSV files in Power Query, I can combine them by appending the four files to cars or boats, let's say, and it will get all of the data on one sheet, but I will have multiple rows for the same customer for the same year, which is not what I need. How did you accomplish it?

 

[sandy666]

did you use From Folder feature?

 

[kmprice710]

did you use From Folder feature?

Data>Get Data>From File>From Folder?
It asks me for a folder path. I saved the XLS file that you uploaded in the same directory as the CSV dummy data.
Here's what I get when I select the folder:

 

[sandy666]

each file load to the power query editor separately then clean them as the steps show
with From Folder feature will be hard to clean all tables at once
result file cannot be in the same folder with *.csv because after refresh result table will read result as additional source

edit
all source files have the same steps so you will not see differences but only with Append1 (result query)

 

[kmprice710]

each file load to the power query editor separately then clean them as the steps show
with From Folder feature will be hard to clean all tables at once
result file cannot be in the same folder with *.csv because after refresh result table will read result as additional source

Okay, so you're saying to:
1- put the 5 csv in a folder together
2- load the power query editor
3- click New Source>File>Text/CSV and load each of the CSV one by one

Can you explain "clean them as the steps show"? When I load them, I'm just clicking ok and taking the suggested changes. Did you do something differently?

 

[sandy666]

gimme a time to create video because it's too much typing here