Search code examples
excelpowerquerytabularexcel-2016

Excel 2016 Convert repeating rows to columns

I have the following data in a column:

ABNANL2A
940
ABNANL2A
:20:ABN AMRO BANK NV
:25:233835613
:28:19901/1
:60F:C110717EUR1333,58
:61:1107160718D12,75N426NONREF
:86:BEA   NR:5FLM01   16.07.11/15.09 OLENBERG EXPL. BURG,PAS018
:62F:C110718EUR1320,83
-
ABNANL2A
940
ABNANL2A
:20:ABN AMRO BANK NV
:25:233835613
:28:20001/1
:60F:C110718EUR1320,83
:61:1107190719D57,87N446NONREF
:86:GEA               19.07.11/07.58 HILTON - HEATHROW HOUNSL,PAS018
GBP 50,00 1EUR=0,8640055 GBP     KOSTEN EUR2,25 ACHTERAF BEREKEND
:62F:C110719EUR1262,96
-
ABNANL2A
940
ABNANL2A
:20:ABN AMRO BANK NV
:25:233835613
:28:20101/1
:60F:C110719EUR1262,96
:61:1107200720C82,N196NONREF
:86:GIRO  45588 taxservice    TEVEELBET. NR.2
MOTORRYTUIGENB.11 *B         *
:62F:C110720EUR1287,31
-

** Note that the text line (#86) can be on 1 or more lines, with linefeed after a couple of characters.*

The original file is longer and what I would like is to transform this with power query in Excel 2016 (without M-code if possible).

I have seen many solutions for transforming tabular tables, but the problem is that certain rows hold column headings as well as data. Basically everything between : per row is the header and the part after is the "value".

I would like to transform this to something like this to:

enter code here

   20             | 25        | 28
 ABN AMRO BANK NV | 233835613 | 19901/1
 ABN AMRO BANK NV | 233835613 | 20001/1
 ABN AMRO BANK NV | 233835613 | 20101/1

Stripping columns and changing the data is no problem. Just converting the repeated rows into columns. Appreciate the help!

Solution

  • The transformation part you've requested can be achieved by pivoting on the column names. Please find a suggestion below how to identify and extract them.

    It would be best to solve the linefeed-problem during import: http://blog.crossjoin.co.uk/2016/02/16/working-with-csv-files-that-contain-rogue-line-breaks-in-power-query-and-power-bi/ otherwise you need some more M-acrobatics in order to solve it.

    let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Spalte1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
    HeaderSplitPosition = Table.AddColumn(#"Added Index", "SplitHeader", each if Text.Start([Spalte1],1)=":" then List.Last(List.FirstN(Text.PositionOf([Spalte1], ":", Occurrence.All),2)) else ""),
    SeparateHeader = Table.AddColumn(HeaderSplitPosition, "Header", each try Text.Trim(Text.Range([Spalte1],0,[SplitHeader]), ":") otherwise ""),
    SeparateBody = Table.AddColumn(
        SeparateHeader,
        "Value",
        each
            try Text.Range(
                [Spalte1],
                [SplitHeader]+1,
                Text.Length([Spalte1])-[SplitHeader]-1
            )
            otherwise [Spalte1]
        //
    )
in
    SeparateBody