1

How can I extract the second table from the first one? I need to extract those titles that have exactly three costs (in the following example: f1 and f3). I tried using pivot tables but could not manage to generate the second table. Any advice would be appreciated!

Screenshot showing desired result

UPDATE: I appreciate that Mike Honey offered a solution. However, since I'm not familiar with Power Query language at all, I am looking for a solution that uses no Add-ins.

There is some general resemblance between this problem and: Excel 2007 transpose/combine multiple rows into one; How to combine values from multiple rows into a single row in Excel?; and excel-2010-move-data-from-multiple-columns-rows-to-single-row. However, this problem differs in that the requirement is not just to transpose and aggregate data, but to do it only for those titles with exactly three costs.

  • Have you tried using pivot tables? By the way, you will need one extra column telling excel which cost(cost1,cost2...) a particular row has. – tumchaaditya Aug 18 '14 at 20:00
  • Those examples are a little different from this requirement. The requirement here is not just to transpose and aggregate data, but to do it only if there are exactly three costs. – fixer1234 Nov 6 '14 at 20:29
  • This question was posed three months ago and the single answer not accepted or upvoted. Do you still need a solution, or has it been overtaken by events and you no longer need it solved, or does Mike Honey's answer solve it? Please update the status so people will know whether to work on a solution for you. If the answer didn't solve the problem, clarify why, which will provide some direction. – fixer1234 Nov 6 '14 at 20:46
  • 1
    @MikeHoney I am sorry Mike, I didn't mean to dismiss your answer and almost forgot about it. I downloaded your prototype and learned from it a a lot. I really appreciate your help buddy. – Meysam Jun 30 '15 at 18:24
2

I would resolve this with the Power Query Add-In. It takes a few steps to get there and a bit of coding in the Power Query language (M) to generate the "running count" needed to get the "cost1/2/3" column headings, and more M code to call the Table.Pivot function (it's not exposed in the Power Query UI).

I've built a prototype which you can view or download - its "Power Query demo - Pivot rows into columns with Running Count.xlsx" in my One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Basically my technique was to add a calculated column to get the "Cost Title" e.g. cost1/2/3. To get this I needed to write a "Running Count" function, to return an Index that resets for each group (title).

I got the outline for the "Running Count" function from this blog post - under "Year-to-Date Sales":

http://cwebbbi.wordpress.com/2013/10/18/implementing-common-calculations-in-power-query/

Then I used the Table.Pivot function to generate a column for each unique value in the destination column.

The documentation for Table.Pivot is here:

http://office.microsoft.com/en-au/excel-help/table-pivot-HA104111995.aspx?CTT=5&origin=HA104122363

Another example of using Table.Pivot is here:

http://cwebbbi.wordpress.com/2013/11/25/pivoting-data-in-power-query/

Finally I filtered out the rows with nothing for cost3.

  • I've updated my demo using the Pivot command which has been added to the current Power Query UI. – Mike Honey Oct 2 '14 at 3:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.