Append data from alternate columns of the same table

{ 9 Comments }

By Ashish Mathur · · , · Leave a comment

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

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • 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!

    Reply

    • Hi,

      Thank you. Please share the link from where I can download your workbook. In the first worksheet, share your 200+ column dataset and in the second worksheet, show the expected result in a few rows.

      Reply

  • 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.2.2.3.1.4”.

    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?

    Reply

    • Hi,

      Which is why I was requesting you for the 200+ column dataset so that I can study some pattern in them and offer an appropriate solution. The data under those columns can all be anonymized.

      Reply

  • 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?

    Reply

  • 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.

    Reply