PQ - extract 8-digit groups from a text string into separate columns
| 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 |
|
| NoD |
|
Group.1 |
Group.2 |
Group.3 |
Group.4 |
8 |
|
00815889 |
|
|
|
|
|
13000934 |
|
|
|
|
|
02526525 |
67802900 |
00103300 |
76816100 |
|
|
08100121 |
08200333 |
08705550 |
|
|
the number of digits is defined in the NOD table
Code:
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
