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

Question

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.

Answer 1

Hey @Ivan Romanenko

This isn't serious :) In the .xlsx you shared the CSV options were mine (Delimiter=";", Encoding=65001) instead of yours (Delimiter=",", Encoding=1252) but that wasn't the most important problem. In each CSV you have 2 different "buckets" of information seperated by blank rows in addition:

I assumed you wanted both "buckets" and revised the queries accordingly

Revised version is avail. here. The only things you have to change:

• The PathToCsvFiles parameter
• If you don't like the term "Bucket" in the report, just rename that column at the end of query "SalesAggregatedPivoted"

I commented the querie's code to help you follow and understand
Hope this is good this time Man, otherwise let me know exactly what output you expect

Answer 2

Hi @Ivan Romanenko ,
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.

Answer 3

@Ivan Romanenko

Download this sample. You will have to:
1 - Update the query parameter (query named "PathToCsvFiles")
2 - Edit the code of query "GetTableFromCsv" to replace the Delimiter, Encoding... values with yours:

tblFromCsv = Csv.Document(Source,  
    [Delimiter=";", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]  

Answer 4

Hey @Ivan Romanenko

The message you get means what it means :) The column "Item Name" doesn't exist in your CSV(s) OR your CSVs are not formatted as in the picture you uploaded yesterday where 4 rows have to be skipped at the top of each file, before we reach the hearder row (Item name, Purchase Count...)

Below is 1 of the files I used and it's exactly what your picture shows:

I really don't see what I could do to help further right now. If you can't sort it out, zip all your CSVs, upload and share the file as you did with your picture