let
Bron = Excel.CurrentWorkbook(){[Name = "OrgTXTfile"]}[Content],
Tekst = Table.TransformColumnTypes(Bron, {{"Kolom1", type text}}),
TekensVerw = Table.SelectRows(
Tekst,
each (
[Kolom1]
<> null
and [Kolom1] <> " "
and [Kolom1]
<> " ------------"
and [Kolom1]
<> " ============"
and [Kolom1]
<> " -------------"
and [Kolom1]
<> " ---------- -----------"
and [Kolom1]
<> " ------------ ---------"
and [Kolom1] <> " ------------- "
and [Kolom1] <> " ====================="
and [Kolom1] <> "-----------------------------------------------------------------------"
and [Kolom1] <> "======================================================================"
)
),
SpatiesVerw = Table.AddColumn(
TekensVerw,
"AlleSpatiesVerw",
each Text.Combine(List.Select(Text.Split(Text.Trim([Kolom1]), " "), each _ <> ""), " ")
),
KolVerw = Table.SelectColumns(SpatiesVerw,{"AlleSpatiesVerw"}),
GesplitstOp1steSpatie = Table.SplitColumn(
KolVerw,
"AlleSpatiesVerw",
Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),
{"Art.Nr.", "Rest"}
),
BovensteRijenVerwijderd = Table.Skip(GesplitstOp1steSpatie, each [#"Art.Nr."] <> "Kostprijs"),
OndersteRijenVerwijderd = Table.RemoveLastN(BovensteRijenVerwijderd, each [#"Art.Nr."] <> "Opbrengst"),
SplitsenOmschrijving = Table.SplitColumn(OndersteRijenVerwijderd, "Rest", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Omschrijving", "Rest.2", "Rest.3", "Rest.4", "Rest.5", "Rest.6", "Rest.7", "Rest.8", "Rest.9"}),
KolSamen = Table.CombineColumns(SplitsenOmschrijving,{"Rest.2", "Rest.3", "Rest.4", "Rest.5", "Rest.6", "Rest.7", "Rest.8", "Rest.9"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Berekeningen"),
nulGefilterd = Table.SelectRows(KolSamen, each ([Berekeningen] <> "") and ([#"Art.Nr."] <> "Totaal")),
UitkomstBerekeningen = Table.SplitColumn(nulGefilterd, "Berekeningen", Splitter.SplitTextByEachDelimiter({" = "}, QuoteStyle.Csv, true), {"Berekeningen", "Totaal"}),
SplitsKol = Table.SplitColumn(UitkomstBerekeningen, "Totaal", Splitter.SplitTextByEachDelimiter({" ("}, QuoteStyle.Csv, false), {"Totaal", "Totaal.2"}),
VerwKol = Table.SelectColumns(SplitsKol,{"Art.Nr.", "Omschrijving", "Berekeningen", "Totaal"}),
TypeGew = Table.TransformColumnTypes(VerwKol,{{"Totaal", type number}}),
SplitsFactor = Table.SplitColumn(TypeGew, "Berekeningen", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"factor", "Berekeningen.2"}),
SplitsPrijs = Table.SplitColumn(SplitsFactor, "Berekeningen.2", Splitter.SplitTextByEachDelimiter({"X "}, QuoteStyle.Csv, false), {"Berekeningen.2.1", "Prijs"}),
SplitsGew = Table.SplitColumn(SplitsPrijs, "Prijs", Splitter.SplitTextByDelimiter(" X ", QuoteStyle.Csv), {"Prijs", "Gewicht"}),
KiesKol = Table.SelectColumns(SplitsGew,{"Art.Nr.", "Omschrijving", "factor", "Prijs", "Gewicht", "Totaal"}),
VerwKg = Table.ReplaceValue(KiesKol," kg","",Replacer.ReplaceText,{"Gewicht"}),
TyperGew = Table.TransformColumnTypes(VerwKg,{{"Gewicht", type number}, {"Prijs", Currency.Type}, {"factor", type number}, {"Art.Nr.", type number}}),
NaamKolGew = Table.RenameColumns(TyperGew,{{"Totaal", "Totaal ink"}})
in
NaamKolGew
