Excel 2016 Convert repeating rows to columns

Question

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!

Sergey Lossev · Accepted Answer · 2016-04-04 02:01:25Z

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

Thanks for the answer! Will look into it and come back with response. Allow some time as calendar is full :)
Worked as charm! Just needed to convert some of the code to local version (Dutch instead of German) of Excel. Mainly "Spalte1" > "Kolom1" ("Column1" for english versions). And also "Tabelle1" > "Tabel1". Rest of code is automatically converted.

Rich74_2013 · Accepted Answer · 2016-03-07 14:14:49Z

The Dutch version of the Excel 2016 query code (just translated, same as from @ImkeF):

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

in
 SeparateBody

Collectives™ on Stack Overflow

Excel 2016 Convert repeating rows to columns

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related