Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MarcusWANG
Regular Visitor

Mapping values in [list] under a list

Hi All

 

I would like a solution to mapping value as below situation:

 

Mapping Table

Mapping DescriptionCategory
*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

DescriptionCategory
00A00BCCategory 1
A00B00C00Category 1
00AC00DCategory 2

 

I tried to use List.Select( Text.Split ([Mapping Description], "*"), each _ <> "") 

Then the Mapping Table turns to below

 

Mapping DescriptionCategory
[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

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

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

dufoq3_0-1718181091002.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

5 REPLIES 5
dufoq3
Super User
Super User

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

dufoq3_0-1718181091002.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

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

dufoq3_0-1718263069761.png

Now you can expand the record

dufoq3_1-1718263157951.png

 

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:
    dufoq3_2-1718263545226.png dufoq3_4-1718263581817.png

     

     

    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

    dufoq3_7-1718264070945.png

     

  • 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 🙂


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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! 

Yes, exactly.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors