Mapping values in [list] under a list

[MarcusWANG]

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

[dufoq3 · Accepted solution]

Hi @MarcusWANG, 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

[MarcusWANG]

Thanks 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

[dufoq3]

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 🙂

[MarcusWANG]

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! 

[dufoq3]

Yes, exactly.