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]

check if this is what you want

s666-PQ-kmprice710.xlsx

Microsoft Excel Workbook

1drv.ms

Sure looks like it. How can I accomplish that for all of the remaining data? How was it done?

 

[sandy666]

How can I accomplish that for all of the remaining data? How was it done?

there are all five csv files so all data and it was done by Power Query

 

[kmprice710]

there are all five csv files so all data and it was done by Power Query

Okay I've never used Power Query but I figured out how to open it and load the five CSVs. What did you do after that or is there a tutorial on what to do in a case like this?

 

[sandy666]

General tut is here with almost everything about PQ

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[kmprice710]

General tut is here with almost everything about PQ

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

Okay. Did you have to use the advanced editor? I sure appreciate that you were able to create the kind of output I need but I still have 100,000 rows to transform. I want to learn how to do it myself.

 

[sandy666]

Did you have to use the advanced editor?

Why? No, everything from the PQE (Power Query Editor) ribbon
if you click step by step in Applied steps window you will see what I did
for each csv file the steps are the same
first you need to clean csv files, no additional spaces, the same format . Each csv table should look the same (structure)
for the Append1 steps is less steps because most of the necessary steps were done before with csv files

100000 rows is not too much

 

[kmprice710]

Why? No, everything from the PQE (Power Query Editor) ribbon
if you click step by step in Applied steps window you will see what I did
for each csv file the steps are the same
first you need to clean csv files, no additional spaces, the same format . Each csv table should look the same (structure)
for the Append1 steps is less steps because most of the necessary steps were done before with csv files

100000 rows is not too much

Thanks for your patience. This is what I see when I open the ribbon and load the file that you uploaded. What do I do next to "click step by step in Applied"?

 

[sandy666]

 

[kmprice710]

See, I do not have all of those steps when I load the xls file provided here. I guess the file that you uploaded did not save the steps?

 

[sandy666]

did you see on the picture which query is selected?