csv with records over multiple lines

I think this one is better to handle a bit more manually than working with the built-in CSV function.

 

What I did was load in every line using Lines.FromBinary, combine all the lines into one big string, then replace "#(lf);;" with just ";" so that the multiple lines that belong together are combined into one.

 

I loaded this big string into a single table cell and split that cell into rows using a double linefeed as the delimiter (this took care of the blank lines). Then I split the resulting rows into columns using a semicolon as the delimiter.

 

To finish up, I promoted the first row to be used as headers and set the column types using Detect Data Type.

let
    Source = Folder.Files("C:\Users\aolson\Downloads"),
    TextFile = Source{[#"Folder Path"="C:\Users\aolson\Downloads\",Name="facturen.txt"]}[Content],
    #"Imported Text" = #table(1,{{Text.Replace(Text.Combine(Lines.FromBinary(TextFile,null,null,1252),"#(lf)"),"#(lf);;",";")}}),
    #"Split Text Into Rows" = Table.ExpandListColumn(Table.TransformColumns(#"Imported Text", {{"Column1", Splitter.SplitTextByDelimiter("#(lf)#(lf)", QuoteStyle.Csv)}}), "Column1"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Split Text Into Rows", "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), 10),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fact.", Int64.Type}, {"Boekstuknr.", Int64.Type}, {"Deb.nr.", Int64.Type}, {"Debiteurnaam", type text}, {"Categorie", type text}, {"Factuurdatum", type date}, {"Vervaldatum", type date}, {"Bedrag excl. btw", type number}, {"Btw", type number}, {"Totaalbedrag", type number}})
in
    #"Changed Type"