PQ - extract 8-digit groups from a text string into separate columns

[sandy666]

RawData

00815889 Land Surveyors;MBE / WBE / DBE; Subcontractor

13000934 Special Construction; Subcontractor

02526525678 Curbing;02900001 Landscaping;03300768 Concrete;16100223 Eleectrical;MBE / WBE / DBE;Subcontractor

08100121 Hollow Metal;08200333 Wood Doors;08705550 Hardware;Supplier


NoDGroup.1Group.2Group.3Group.4


800815889

13000934

02526525678029000010330076816100

081001210820033308705550



In this case RawData contains groups of 8 digits
the number of digits is defined in the NOD table

let
NoD = Excel.CurrentWorkbook(){[Name="NOD"]}[Content][NoD]{0},
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
TS = Table.AddColumn(Source, "Group", each Text.Select([RawData],{"0".."9"})),
IDC = Table.TransformColumns(Table.AddColumn(TS, "Length", each Text.Length([Group]), Int64.Type), {{"Length", each Number.IntegerDivide(_, NoD), Int64.Type}}),
RC = Table.RemoveColumns(IDC,{"RawData"}),
Split = Table.RemoveColumns(Table.SplitColumn(RC, "Group", Splitter.SplitTextByRepeatedLengths(NoD), List.Max(RC[Length])),{"Length"})
in
Split