Generate a list of values from a range of poscal codes

[Anonymous]

Hi,

I have 2 tables that I need to combine (examples of the tables are below).

- Table A contains the company name and the target area based on postal code and described as ranges of postal codes.
- Table B contains the name of the township and the accompanying postcal code.

What I need to do is that in table A I need to transform the range of postcal codes to individual lines of a single postal code. And of course the result could be that I get 15 lines of company A because the range consist of 15 postcal codes.

After this is done I can connect the company name with the name of the township based on postal code. And the result of this should be that per company I get a list of distinct townships (that is because large townships consists of more than 1 postal code).

I should probably use one of the M list functions to transform my range of postal codes to a number of columns per company with an individual postal code. But I don't seem to get it working.

Desired end result
Company name	Township name
A	L;G;Z;W
B	D
C	A;C
D	B;T;U
E	O
F	P

Table A			Table B
Company name	Postal code		Township name	Postal code
A	1000-1400;1201-1220;1250-1271		D	1201
B	1453-1469		D	1202
C	1701-1720;1721-1743		D	1203
			D	1204
			E	1468
			F	1701
			F	1702
			F	1703
			F	1704
			F	1705

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

You could refer to below code in M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcrBDYAwDATBXu6dSD7HwUK8gDKs9N8GOX6r1VThRgPNrDPMLrqx01019/MkVis8UjHHVsf5n1cnpVM6XRUDa30=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Company name" = _t, #"Postal code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company name", type text}, {"Postal code", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Postal code", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Postal code.1", "Postal code.2", "Postal code.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Postal code.1", type text}, {"Postal code.2", type text}, {"Postal code.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Company name"}, "Attribute", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", Int64.Type}, {"Value.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each List.Numbers([Value.1],[Value.2]-[Value.1]+1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Attribute", "Value.1", "Value.2"})
in
    #"Removed Columns"

Rsult:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft ,

Thx for the list function!

I still got 2 questions.

1. Why do you use each?

2. Some companies in my table have multiple ranges. Each time I split a column by - I get columns like "Value1.1", "Value1.2", "Value2.1", "Value2.2", "Value3.1" etc.  Is is possible for the list function to work on all of those columns instead of the 1th 2 as it does now?

[Anonymous]

The problem is that e.g. 1 company has 46 postal code ranges.
I can split al those columns with the split column option in the query editor (apparently you can only do it for 1 column at a time). This will create 92 columns in total with seperate postal codes in my model.

But then I have the problem that I need to apply the function you provided to all 92 columns at once. And I have no idea how...

[Anonymous]

I should split the columns not to columns but to rows. 

Than the above mentioned M formula works.