Forum Discussion
Mapping values in [list] under a list
Hi All
I would like a solution to mapping value as below situation:
Mapping Table
| Mapping Description | Category |
| *A*B*C* | Category 1 |
| *A*D* | Category 2 |
Asterisk "*" here is for Excel's function which means matching any sequence of characters
According to the Mapping File, I would like to mapping the Category to a working file as below:
Working File
| Description | Category |
| 00A00BC | Category 1 |
| A00B00C00 | Category 1 |
| 00AC00D | Category 2 |
I tried to use List.Select( Text.Split ([Mapping Description], "*"), each _ <> "")
Then the Mapping Table turns to below
| Mapping Description | Category |
| [List] | Category 1 |
| [List] | Category 2 |
[List] contains the key text, such as {"A", "B", "C"} for Category 1, {"A","D"} for Category 2
However, I have no idea how to let the working file transforming every text in [List] under Mapping Description to match the results.
Thanks
Hi Anonymous, check this:
I've added 1 more row to sample data to determine: if order is different (for row no 4 it is B then A then C) there is no match.
Result
let DataTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBwNDBwclaK1YlWArEMDJwNDMA8oAyQ6QJlO4G4SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]), MappingTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0nLUctJy1lLSUXJOLElNzy+qVDBUitUBS7igCBspxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Mapping Description" = _t, Category = _t]), Ad_MappingList = Table.AddColumn(MappingTable, "MappingList", each List.Select(Text.Split([Mapping Description], "*"), (x)=> x <> ""), type list), MappingListsBuffer = List.Buffer(Ad_MappingList[MappingList]), Ad_Category = Table.AddColumn(DataTable, "Category", each [ a = MappingListsBuffer, b = List.Transform({0..List.Count(a)-1}, (x)=> List.Accumulate( {0..List.Count(a{x})-1}, {}, (s,c)=> s & {Text.PositionOf([Description], a{x}{c}, Occurrence.First)})), c = List.Transform(b, (x)=> if List.Contains(x, -1) or x <> List.Sort(x) then null else 1), d = List.PositionOf(c, 1, Occurrence.First), e = try MappingTable[Category]{d}? otherwise null ][e], type text) in Ad_Category
5 Replies
Hi Anonymous, check this:
I've added 1 more row to sample data to determine: if order is different (for row no 4 it is B then A then C) there is no match.
Result
let DataTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBwNDBwclaK1YlWArEMDJwNDMA8oAyQ6QJlO4G4SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]), MappingTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0nLUctJy1lLSUXJOLElNzy+qVDBUitUBS7igCBspxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Mapping Description" = _t, Category = _t]), Ad_MappingList = Table.AddColumn(MappingTable, "MappingList", each List.Select(Text.Split([Mapping Description], "*"), (x)=> x <> ""), type list), MappingListsBuffer = List.Buffer(Ad_MappingList[MappingList]), Ad_Category = Table.AddColumn(DataTable, "Category", each [ a = MappingListsBuffer, b = List.Transform({0..List.Count(a)-1}, (x)=> List.Accumulate( {0..List.Count(a{x})-1}, {}, (s,c)=> s & {Text.PositionOf([Description], a{x}{c}, Occurrence.First)})), c = List.Transform(b, (x)=> if List.Contains(x, -1) or x <> List.Sort(x) then null else 1), d = List.PositionOf(c, 1, Occurrence.First), e = try MappingTable[Category]{d}? otherwise null ][e], type text) in Ad_CategoryThis is not easy to understand but you can delete/change this part of code and then expand the record with every sigle step (a, b, c, d and e are just steps... I could use block let in but usualy I use it as a record which is the same)
delete [e] and replace type text to type record
Now you can expand the record
Description for steps a to e
- a - this just refers to MappingListBuffer (this step is not mandantory bud I did it this way)
- b - this one is complex
lets look at inner List.Accumulate: it returns position of every letter A, B and C from MappingListsBuffer like this:
outer List.Transform do this for each list in MappingListBuffer - so for every row there are to lists because we have two lists in MappingListBuffer. This is result of 2nd from MappingListBuffer for 1st row of [Description] column
- c - returns 1 if list from b step does not contains -1 (so there is no missing letter from MappingListBuffer) and list from b output equals sorted list b (this we need to check if sort order of letter is the same). If not it returns null
- d - find first position of 1 from c (which is position of [category] from MappingTable we want to return)
- e - returns correct position of [category] from MappingTable
I hope it makes sense a bit for you 🙂