question

IvanRomanenko-5569 avatar image
0 Votes"
IvanRomanenko-5569 asked ·

Combining daily sales data that's categorized by a date together using Power Query

Hello,
I am trying to set up a monthly report flow using Power Query.
I have 30csv files with sales data in each (Item Name | Sale Count | Total Value Sold, etc.) for every day of the month.

What I am trying to achieve is to combine them all together with the following template:
First line: April 1, April 2, ....
Second line: Item A 5 12 .....

Previously I have done that manually combining all item names together into one column, then removing duplicates, and afterwards using vlookup matching the item names to a corresponding sale count #, however, it proved to be very complicated and eats a lot of time. Is there a way to optimize Power Query functions to do that for a set of files? The most confusing aspect is how to combine data horizontally (not append vertically); and how to have only unique names when appending together.


office-excel-itpro
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @IvanRomanenko-5569

I might be able to help... Could you:
1 - Upload a picture of the desired output with say, 2 dates and 2 items and a few values you want to report (Sales count, Sales Total...)
2 - If you could upload and share (i.e. with OneDrive) one Csv that would help
3 - Explain how you get the Date of each report. Is it in the Csv file name (if so show an ex. of a file name)?
4 - Is a Pivot Table an option?
5 - Does your Excel version inc. Power Pivot?


0 Votes 0 ·

Hi @IvanRomanenko-5569,
Just checking in to see if the information was helpful. Please let us know if you would like further assistance.


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 Votes 0 ·

1 Answer

emizhang-msft avatar image
0 Votes"
emizhang-msft answered ·

Hi @IvanRomanenko-5569,
Did mean combine several Workbooks by Power Query?
I suggest you refer to this support article about how to Combine Data from Multiple Workbooks in Excel (using Power Query):
https://trumpexcel.com/combine-data-from-multiple-workbooks/
Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.





·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.