Append data from alternate columns of the same table


On the Data worksheet, data for payments received from different customers is shown invoice wise.  Since payments are received in tranches, they are shown in different columns - column C and D show dates and amounts received in the first tranche respectively and column E and F show dates and amounts received in the second tranche respectively.

One may want to generate the list of Invoice Number, Customer Name and the Amount for a particular date.  Since dates are split across two columns, it will not be possible to filter the dataset.  In order to filter the dataset, one will have to append data from alternate columns.

I have been able to accomplish this by using Power Query - a free add-in from Microsoft for Excel 2010 and higher versions.

You may refer to my solution in this workbook.

You may watch a short video of my solution here

RSS 9 Comments…

 Share your views
  1. Hi Ashish,

    Very nice video about appending columns in the same table using power query, I've also been doing the method you described, but now I'm facing an issue, I have 200+ columns that I need to append in groups of three, so creating one query for each group doesn't sound practical. Do you have any better way of doing it?

    I've been working on a query and it returns me values in the following format:

    Date | Time | Value | Time | Value...

    A | B | C | D | E...

    But I need to transform it to look like:

    Date | Time | Value

    A | B | C

    A | D | E

    Thanks for the help!

  2. Hi Ashish,

    Thank you for your willingness to help, I can't share the file due to information security issues and it would take me forever to anonymize the data in order to share it with you.

    Nonetheless, I can share a file that would suffice:!Akgc0bNcXpGhg5505LnymPvqhtlLGQ?e=5Vqvcz

    Thank you very much!

  3. Hi Ashish,

    Thank you very much for your solution, it works perfectly!

    I have only one issue, all my 200+ columns have each a unique random name, like "Body.Text.".

    Before I use your solution, I need to fix the column names, to be in the format:

    Date, Time1, Value1, Time2, Value2 up until my last column.

    Can you help me with that?

  4. I updated the excel file with the actual column names and the desired ones.

    Is there a more clever way to do this using M or do I really have to change the names one by one using the UI before using the solution you described?

  5. Hi Ashish,

    Thanks for your help, I took care of the column renaming and implemented your solution and it works great! Now I have the M code of it, so I can replicate it if needed again.

Leave a Comment

Your email address will not be published.