none
Combine 2 rows into 1 RRS feed

  • Question

  • I have a task to scrap out promotional data from a TXT file into Excel so I can use it.  I have used PowerQuery to clean up the TXT file - it is now close. The problem I have is that I have (say) 600 rows in my sheet as follows (simplified)

    Product Code |  Case Sell  |  Buy Period    |

    1234             |   $33.80    |  30/12/2013  |

                        |     $1.69    |  12/1/2014    |

    4567             |    31.84     |   30/12/2013  |

                        |     1.99      |  12/1/2014    |

    and so on.   So every first row has a full product description, plus the case price and buy start date.  Every Second row has no product information, the unit sell price, and the buy end date.  Every Second row belongs to the previous row.  I would like to flatten this data into 5 columns (plus all the other columns I have not refered to here). Can anyone help me with this problem?

    Wednesday, April 2, 2014 4:04 AM

Answers

  • Does the header also span two rows or is it only a single row? If it has only a single row, you could do something like this:

    let
        Input = {
            "Product Code |  Case Sell  |  Buy Period    |",
            "1234         |     $33.80  |  30/12/2013    |",
            "             |      $1.69  |  12/1/2014     |",
            "4567         |      31.84  |  30/12/2013    |",
            "             |       1.99  |  12/1/2014     |"
        },
        Combined = List.Generate(
            () => 0,
            (i) => i < List.Count(Input),
            (i) => if i = 0 then 1 else i + 2,
            (i) => if i = 0 then Input{0} else Input{i} & Input{i + 1})
    in
        Combined
    

    Your Input value would obviously come from something like Lines.FromBinary instead of hardcoded text.

    Wednesday, April 2, 2014 1:59 PM

All replies

  • Does the header also span two rows or is it only a single row? If it has only a single row, you could do something like this:

    let
        Input = {
            "Product Code |  Case Sell  |  Buy Period    |",
            "1234         |     $33.80  |  30/12/2013    |",
            "             |      $1.69  |  12/1/2014     |",
            "4567         |      31.84  |  30/12/2013    |",
            "             |       1.99  |  12/1/2014     |"
        },
        Combined = List.Generate(
            () => 0,
            (i) => i < List.Count(Input),
            (i) => if i = 0 then 1 else i + 2,
            (i) => if i = 0 then Input{0} else Input{i} & Input{i + 1})
    in
        Combined
    

    Your Input value would obviously come from something like Lines.FromBinary instead of hardcoded text.

    Wednesday, April 2, 2014 1:59 PM
  • Have you tried using the FillDown function on the Product Code column ? You would get the product code on every line. You might then be able to do the flattening in a pivot table based on the query.
    Thursday, April 3, 2014 4:34 PM
  • I did see the fill down tool. I tried it quickly but it didn't behave as I expected. I will take a closer look.

    Curt, thanks for your suggested code.  I will take a look.  Where did you learn to code like that for Power Query?  I can't find any documentation that explains how to do this.

    Sunday, April 6, 2014 10:44 PM
  • I'm on the product team and I work on the language and library, so it's my job to know these things :). You can find links for the Power Query language and library reference at http://office.microsoft.com/en-us/excel-help/learn-about-power-query-formulas-HA104003958.aspx

    While we generally hope that people are able to perform all their common tasks via the Power Query UI, we know there are a lot of edge cases (like yours) where no amount of standardized data manipulation is likely to cover your requirement. That's why the queries are implemented in the underlying M language, and that's why we let users write M code directly.

    Monday, April 7, 2014 2:08 AM
  • Ahh, thanks Curt. That explains a lot. I guess with a new tool like Power Query, there is not the same quantity of learning materials out there (as opposed to reference materials).  Thanks for the links.
    Monday, April 7, 2014 2:23 AM