• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Workbook Data Merging

Jean Stewart

Jean Stewart

New Member
Hello

A few years ago in a previous job I used this forum all the time and everyone was so helpful I'm hoping for the same again. I have a new job and part of the reporting that I have to do is "mirror sales" so I've downloaded 2017 - 2019 year by year, popped them onto a workbook but don't know how to merge them all together to have a proper overview of what customers have bought in previous year. I welcome any advice

Thank you in advance
 

Attachments

  • July - September 2018.xlsx
    57 KB · Views: 6
OK - you can use PowerQuery for this. How did you import the data? Was it from separate files? If so, can you attach ONE of the feeder files here?
 
Hi Ali

When you say feeder files do you mean my original export? Excuse the delay in reply I'm on a few half days this week...

Thanks for your help
 
I mean the files you say you downloaded, which presumably are in much the same format with matching columns.
 
AliGW said:
I mean the files you say you downloaded, which presumably are in much the same format with matching columns.
Click to expand...
Yes the file have been exported from system and the columns that are not required removed. We use DEAR and the inbuilt reports are not the best ..

Do you still need the original files?
 
In the attachment I converted each of the data tables into real Excel tables by selecting them and choosing Insert | Table. Then I created a new blank query and used this M Code to create a composite table of all years' data:

Code: 
let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Table")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Order Date", "Invoice Date", "Customer", "SKU", "Product", "Quantity Total"}, {"Order Date", "Invoice Date", "Customer", "SKU", "Product", "Quantity Total"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Content",{"Name"})
in
    #"Removed Columns"

We should be able to adapt this to pull the data directly from the source files instead of you having to copy and paste.
 

Attachments

  • PQ July - September 2018 AliGW.xlsx
    98.3 KB · Views: 4
AliGW said:
In the attachment I converted each of the data tables into real Excel tables by selecting them and choosing Insert | Table. Then I created a new blank query and used this M Code to create a composite table of all years' data:

Code: 
let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Table")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Order Date", "Invoice Date", "Customer", "SKU", "Product", "Quantity Total"}, {"Order Date", "Invoice Date", "Customer", "SKU", "Product", "Quantity Total"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Content",{"Name"})
in
    #"Removed Columns"

We should be able to adapt this to pull the data directly from the source files instead of you having to copy and paste.
Click to expand...
Hi Ali

Thank you for the example, I think I maybe haven't explained myself very well, what I am hoping to achieve is a report on what the customer has bought over the last 3 year, see what product range in order to replicate the same sale again for 2020. I want to be able to use this data as a simple way to help with forward planning for our production plant. The "report" I would hope to be able to send to the client to ask for forcasting for the next 3 months.

This is something I have been tasked with and it may not even be possible.

I've attached an exported file of 2019 data from the DEAR system
 

Attachments

  • Sales by Product Details Report.xlsx
    42.9 KB · Views: 5
OK - then before I go any further, please mock up the worksheet you want to create so I can see what we are aiming for.
 
Thanks Ali I'll have a bash.. just a thought, (and totally down to my own inexperience) would this be easier with an annual data report over 2 year? Let you have a play around with it and see what you can come up with? I basically would like to run a report on the data that will give me enough history over a selected period of time to approach the client and say you normally buy these items in say October, will you be repeating this order?

Please tell me if I make no sense at all, appreciate your patience
 
Why would I want to have a play with it and see what I come up with? How would that help? The idea here is you come to us with a clear idea of what you want and then we help you to a solution.

I'll stand down until you can give us something more concrete. Really sorry, but I don't have time to do your thinking for you. :)
 
AliGW said:
Why would I want to have a play with it and see what I come up with? How would that help? The idea here is you come to us with a clear idea of what you want and then we help you to a solution.

I'll stand down until you can give us something more concrete. Really sorry, but I don't have time to do your thinking for you. :)
Click to expand...
Completely understand .. I'll go think then come back thank you
 
You must log in or register to reply here.
Back
Top