Postcode within range

[Gwill1983]

Hi all,

I have been supplied a list of postcodes setup in ranges which are allocated to a zone. I need to split these to show each individual postcode and what zone these are allocated to.

Before I go through manually to do this, I wanted to see if anybody knew if there was a way to allocate individual postcodes to a zone within a range.

For example,

The sheet that I have been provided looks as below

Postcode	Zone
BD 1-22, 97, 99	3

On a seperate sheet I need to list each BD postcode individually noting that each of these is allocated to zone 3. Is there a quick way to do this rather than manually going through the list?

Thanks

Chris

 

[lrobbo314]

How about this?

Book1.xlsb
	A	B	C	D	E	F
1	Postcode	Zone		Postcode.1	Postcode.2	Zone
2	BD 1-22, 97, 99	3		BD	1	3
3	XY 1-5, 7, 11-14	4		BD	2	3
4				BD	3	3
5				BD	4	3
6				BD	5	3
7				BD	6	3
8				BD	7	3
9				BD	8	3
10				BD	9	3
11				BD	10	3
12				BD	11	3
13				BD	12	3
14				BD	13	3
15				BD	14	3
16				BD	15	3
17				BD	16	3
18				BD	17	3
19				BD	18	3
20				BD	19	3
21				BD	20	3
22				BD	21	3
23				BD	22	3
24				BD	97	3
25				BD	99	3
26				XY	1	4
27				XY	2	4
28				XY	3	4
29				XY	4	4
30				XY	5	4
31				XY	7	4
32				XY	11	4
33				XY	12	4
34				XY	13	4
35				XY	14	4
Sheet5

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitBD = Table.SplitColumn(Source, "Postcode", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Postcode.1", "Postcode.2"}),
    SplitNum = Table.ExpandListColumn(Table.TransformColumns(SplitBD, {{"Postcode.2", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Postcode.2"),
    ToList = Table.TransformColumns(SplitNum,{{"Postcode.2", (val)=>
        let 
            l =  List.Transform(Text.Split(val,"-"),Number.From),
            a = {l{0}..l{List.Count(l)-1}}
        in
            a
    }}),
    Expand = Table.ExpandListColumn(ToList, "Postcode.2")
in
    Expand

 

[Gwill1983]

Thanks. I have copy and pasted your solution into the formula tab in power query but it comes up with the error below:

All I have changed is the table name in the source part to match my table name (Raw_Data)

Am I missing something?

 

[lrobbo314]

When you get into Power Query, look on the 'Home' tab and find where it says 'Advanced Editor'. That's where you will need to paste all of the code.

 

[Gwill1983]

Aha! Thanks for the update.

It almost works! One thing that I haven't considered is that some of the postcode entries don't have ranges. For instance,

Postcode	Zone
AB	9

I think it might be these that are causing the error as looking at the query steps now these are showing as null in the postcode 2 column (snip below)

The step after this is the ToList step which is showing the errors

 

[lrobbo314]

How about this?

let
    Source = Excel.CurrentWorkbook(){[Name="Raw_Data"]}[Content],
    SplitBD = Table.SplitColumn(Source, "Postcode", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Postcode.1", "Postcode.2"}),
    SplitNum = Table.ExpandListColumn(Table.TransformColumns(SplitBD, {{"Postcode.2", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Postcode.2"),
    ToList = Table.TransformColumns(SplitNum,{{"Postcode.2", (val)=>
        let 
            l =  try List.Transform(Text.Split(val,"-"),Number.From) otherwise val,
            a = try {l{0}..l{List.Count(l)-1}} otherwise val
        in
            a 
    }}),
    Expand = Table.ExpandListColumn(ToList, "Postcode.2"),
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Expand, {{"Postcode.2", type text}}, "en-US"),{"Postcode.1", "Postcode.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Postcode")
in
    Merge

Book1
	A	B	C	D	E
1	Postcode	Zone		Postcode	Zone
2	BD 1-22, 97, 99	3		BD 1	3
3	AB	9		BD 2	3
4	XY 1-5, 7, 11-14	4		BD 3	3
5				BD 4	3
6				BD 5	3
7				BD 6	3
8				BD 7	3
9				BD 8	3
10				BD 9	3
11				BD 10	3
12				BD 11	3
13				BD 12	3
14				BD 13	3
15				BD 14	3
16				BD 15	3
17				BD 16	3
18				BD 17	3
19				BD 18	3
20				BD 19	3
21				BD 20	3
22				BD 21	3
23				BD 22	3
24				BD 97	3
25				BD 99	3
26				AB	9
27				XY 1	4
28				XY 2	4
29				XY 3	4
30				XY 4	4
31				XY 5	4
32				XY 7	4
33				XY 11	4
34				XY 12	4
35				XY 13	4
36				XY 14	4
Sheet1

 

[Gwill1983]

Thats brilliant!

However, it doesn't work on postcodes which include letters (ie London postcodes)

Getting the error below

This is for the post codes E1-1W.

Any thoughts how to avoid these? There arent many so can be a manual entry, but this is stopping the lists being extended so returning as below:

 

[Gwill1983]

Hi, I have been playing with this all morning to find a solution, but I am really struggling.

Does anybody have any further ideas how to solve this?

I have tried to remove the postcodes that include text at the end, but then I have the problem of trying to do a partial look up which creates another problem!

Any thoughts?

 

[lrobbo314]

I probably can't help till Sunday, but it would be helpful if you could post a good mix of sample data.

 

[Gwill1983]

Thanks for your help.

The data I receive is as below with postcode range in the first column, and zone in the second:

AB	9
AL	32
B (excluding B1-5)	1
B 1-5	101
BA (excluding BA1)	5
BA 1	105
BB 0-6, 10-12, 94	1
BB 7-9, 18	2
BD 1-22, 97, 99	3
BD 23-24	4
BH	32
BL	1
BN 1-2	36
BN 3, 11-18, 21-24, 26-27, 41-45, 50-52, 88	31
BN 5-10, 20, 25	6
BR	31
BS 1-21, 30-41, 48-49, 80, 98-99	L
BS 22-29	L
CA	6
CB 1-3, 8-11, 21-23	32
CB 6-7, 24-25	4
CB 4, 5	5
CF 3, 5, 10-11, 14-15, 30, 37-40, 46, 61-99	3
CF 31-36, 41-45, 47-48	5
CH	1
CM 0, 3, 8-9	31
CM 1-2	5
CM 4-7, 11-16, 77, 92, 98-99	32
CM 17-24	4
CO 1-3, 5, 7, 11-16	31
CO 4, 6, 8-10	32
CR 0, 2-3, 5-6, 8-9, 44, 90	31
CR 4, 7	36
CT	31
CV (except 31-37, 47)	1
CV 31-37, 47	5
CW	1
DA	31
DD	8
DE	1
DG	7
DH	4
DL	4
DN 1-7, 10-12, 22, 55	1
DN 8-9, 14-21, 38-39	2
DN 31-37, 40-41	3
DT	6
DY	1
E 2-4, 5-18, 20, 77, 98	37
E 1 - 1W	38
EC 1-4, 50, 99	29
EC 88	38
EH	7
IG	31
IM	24
IP 1-33	32
IP 98	33
IV 1-28, 30-32, 36, 55-56, 63, 99	9
IV 40-49*, 51-54 (excludes island of Scalpay IV49)	17
JE (Import tax may apply, please call)	25
KA 1-26, 29-30	7
KA 27	22
KA 28	22
KT 1-8, 10, 12, 17-19, 21	6
KT 9, 11, 13-16, 20, 22-24	31
KW 1-3, 5-14	9
KW 15*-17* (exludes islands of Hoy, Flotta, Graemsay, Barray, South Ronaldsay)	18
KY 2-5	7
KY 1, 6-16, 99	8
L	1
LA1-10	4
LA11-23	5
LD	7
LE	1
LL11-14	2
LL15-36, 40-41, 57	6
LL37-39, 42-49, 51-56, 58-78	7
LN 1-8	3
LN 9-13	4
LS 10-11, 20-27	1
LS 1-9, 12-13, 14-25, 28-29, 88, 98, 99	2
LU	32
M	1
ME	31
MK 1-15, 17-19, 77	32
MK 16, 43, 46	4
MK 40-42, 44-45	5
ML 1-7	7
ML 8-12	7
N1-2, 4-8, 10, 15-17, 19, 22	37
N 3, 9, 11-14, 18, 20-21, 81	36
NE	4
NG	1
NN	1
NP (except 15-16, 25)	3
NP 15-16, 25	5
NR 1-10, 13-23, 29-25, 99	12
NR 11, 12, 24-28	5
NW 1, 3, 5, 6, 8	37
NW 2, 4, 7, 9-11, 26	36
OL	1
OX 1-5, 10-11, 13-18, 25-29, 33, 39, 44, 49	32
OX 7, 12	5
PA 1-19	7
PA 20	19
PA 21-38	21
PA 41-49, 60-78, 80	22
PE 1-5, 8-9, 99	1
PE 6-7, 10-11	2
PE 12, 13-16, 20-21	3
PE 22-25, 30-38	4
PE 19, 26-29	33
PH 1-7, 11-14	8
PH 8-10, 15-18	9
PH 19, 20-26, 30-39, 40-41, 49-50	10
PH 42-44	22
PL	7
PO 1-17	32
PO 18-22	31
PO 30-41	23
PR	2
RG	31
RH 1-3, 7-9	6
RH 4-5, 12-20, 77	31
RH 6, 10-11	36
RM	32
S	1
SA 1-18, 80, 99	6
SA 19-20, 31-39, 40-48, 61-69, 70-73	7
SE 1, 11, 16-17	38
SE 2, 6, 9, 12, 18-21, 25-28	36
SE 1P, 3-5, 7-8, 10, 13-15, 22-24, 99	37
SG	5
SK	1
SL	33
SM 1, 3, 4	36
SM 2, 5-7	31
SN	5
SO	4
SP	32
SR	4
SS	31
ST	1
SW 1, 3, 5-7, 10	28
SW 2, 4, 8-9, 11-20, 95, 99	37
SY 1-6, 12-14, 99	2
SY 7-9	4
SY 10-11, 15, 16-25	7
TA 1-3, 6-20	5
TA 4-5, 21-24	6
TD 1-4, 6-7, 9-10	7
TD 5, 8, 11-15	8
TF	1
TN 1-7, 9-12, 17-40	31
TN 8, 13-16	6
TQ	7
TR 1-9	7
TR 10-27, 93	8
TR 21** - 25** (Scilly Isles - mainland only onward shipping to Scilly Isles not inc' please call for more details)	8
TS	4
TW 1-8, 11-20	32
TW 9-10	31
UB	33
W 1A, B, C, G, H, J, K, M, N, P, R, S U, V, W, X, Y, 2, 6, 8-11	37
W 3-5, 7, 12-13	31
W 1 D, F, T	28
WA	1
WC	29
WD	32
WF	1
WN	1
WR	1
WS	1
WV	1
YO (except 7, 17-18, 21-22, 60-62)	3
YO 7, 17-18, 21-22, 60-62	4
ZE * (Excludes islands of Yell, Unst, Fetlar, Foula, Fair, Isle, Virkie)	20

The main difficulty that I am having is with all of the additional text included, and the postcodes with characters at the end rather than numbers.

In an ideal world, I would paste this into my sheet and power query would spit out something as per the below:

Postcode	Zone
AB1	9​
AB10	9​
AB11	9​
AB12	9​
AB13	9​
AB14	9​
AB15	9​
AB16	9​
AB2	9​
AB21	9​
AB22	9​
AB23	9​
AB24	9​
AB25	9​
AB3	9​
AB30	9​
AB31	9​
AB32	9​
AB33	9​
AB34	9​
AB35	9​
AB36	9​
AB37	9​
AB38	9​
AB39	9​
AB4	9​
AB41	9​
AB42	9​
AB43	9​
AB44	9​
AB45	9​
AB5	9​
AB51	9​
AB52	9​
AB53	9​
AB54	9​
AB55	9​
AB56	9​
AB9	9​
AB99	9​
AL1	32​
AL10	32​
AL2	32​
AL3	32​
AL4	32​
AL5	32​

I dont think this is possible so think some manual intervention might be required, but struggling to do anything with the text elements.

Your suggestions so far have worked perfectly for the 'standard' postcode ranges.

Thanks