Forum Discussion
Mapping values in [list] under a list
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
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_CategoryThanks dufoq3
I transferred your code to my case successfully.
I am trying to understand the purpose of each step. I am trying split steps by a,b,c,d,e. However, it doesn't work if I split the step. So I can't see the result of each step..
Can you breifly explain the purpose of b,c,d,e?
Thanks
This 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 🙂
Oh, I got you now. Impressive!
And I guess the "d-find first position of 1" means, if [Description] matches multiple [MappingList], it will return the first matched category and will no longer to check the remainings?
Thank you!