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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iamprajot
Responsive Resident
Responsive Resident

M experts - Search a list of values in each cell of a column and return a particular value.

M experts please help here! @ImkeF@Greg_Deckler 

I have a column of Paper Materials(below) that contains Material Weight.

for e.g. "kreda mat250gsm" is having "250gsm" as Material Weight. The order/sequence of these 2 things in a cell is not fixed.

Job IdMaterials
12.95 and 2.64 mm M.S wire
23mm PVC
8135 grs hv silk mc
10paper 120 gsm
12PS 2mm
14kreda mat250gsm
14Material: 70gsm FSC woodfree paper
16115gsm Silk
17matte coated paper 250GSM
18mat kuse135gr
2380 gsm Ozone fsc mixed credit
24Cartulina gráfica 400 gr
25Vinil branco blackout 0.10 mm
29SINTRA 3mm
310.6mm Gloss Vinyl Coated Magnetic
34200 gr/qm Bilderdruck matt

 

Then I created a LIST of values(below) that need to be searched in that Material column and based on this I could extract the Material Weight. I created this LIST so that it could be expanded based on more matching values.

Search
mil
mm
gsm
gr

 

Now with these 2 things (1 table and 1 list), I want to create a function that takes this LIST as an input and search the values of this LIST in each cell of the Material column and return the exact material weight like below.

Job IdMaterialsResult
12.95 and 2.64 mm M.S wire2.64 mm
23mm PVC3 mm
8135 grs hv silk mc135 gr
10paper 120 gsm120 gsm
12PS 2mm2 mm
14kreda mat250gsm250 gsm
14Material: 70gsm FSC woodfree paper70 gsm
16115gsm Silk115gsm
17matte coated paper 250GSM250 gsm
18mat kuse135gr135 gr
2380 gsm Ozone fsc mixed credit80 gsm
24Cartulina gráfica 400 gr400 gr
25Vinil branco blackout 0.10 mm0.10 mm
29SINTRA 3mm3 mm
310.6mm Gloss Vinyl Coated Magnetic0.6 mm
34200 gr/qm Bilderdruck matt200 gr

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Hi @iamprajot 

please paste the following code into the advanced editor and follow the steps:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PZHNUoMwFIVf5QzrDkIo/XGnzNhxgXbE6abTRRoCZkigBrDq2/gsvpg3hHF5z/1ycnJyPAZxsAhYuE3B2xIsXC1hDPKwwFVZGZwWx4ARkZC4P2TTvKE5TlLUtsfbB3qlGxgxreKIdhd+kRYxi1D3xsvOYl+AmXle0txYWXIYPrA0+gfdIueDtIrrW6zdAg9FhmvXlZWVEpO5Z1cuR5w6pKAMXlyTSJ6DhOjIp/QHQHfsitwjG4+gGXtJ76i9HUtI3kyZ8fzdtRJVL2DUJ3kIiqoGj7mEGbfDqFXLqYPfn0oJjmVER2enlJCDapXG2fJWdDhrLppuHBCFcYS5BLYlrHh8en25QzJrifuNKFxR2zvd9T3I5ksj80/Jed3KQfmqExeETbfevBvcK11KW9pRNK5TCnv6Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Job Id" = _t, Materials = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Id", Int64.Type}, {"Materials", type text}}),
    #"Search Terms" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wys3MUYrVAdK5YCq9GEoXKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Search = _t]),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #"Search Terms"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Search"}, {"Search"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Position", each Text.PositionOf(Text.Lower([Materials]),Text.Lower([Search]))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Position] <> -1)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Number", each Text.Range([Materials],0,[Position])),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom2",{{"Number", Text.Trim, type text}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Trimmed Text", {{"Number", each Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Trimmed Text1" = Table.TransformColumns(#"Extracted Text After Delimiter",{{"Number", each Text.Select(_, {"0".."9", ",", "."}), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Trimmed Text1",{"Number", "Search"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

 

You'll see that #24 is a problem. If you come up with a rule that works, let me know.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

8 REPLIES 8
iamprajot
Responsive Resident
Responsive Resident

M experts please help here! @ImkeF , @Greg_Deckler 

I have a column of Paper Materials(below) that contains Material Weight.

for e.g. "kreda mat250gsm" is having "250gsm" as Material Weight. The order/sequence of these 2 things in a cell is not fixed.

Job IdMaterials
12.95 and 2.64 mm M.S wire
23mm PVC
8135 grs hv silk mc
10paper 120 gsm
12PS 2mm
14kreda mat250gsm
14Material: 70gsm FSC woodfree paper
16115gsm Silk
17matte coated paper 250GSM
18mat kuse135gr
2380 gsm Ozone fsc mixed credit
24Cartulina gráfica 400 gr
25Vinil branco blackout 0.10 mm
29SINTRA 3mm
310.6mm Gloss Vinyl Coated Magnetic
34200 gr/qm Bilderdruck matt

 

Then I created a LIST of values(below) that need to be searched in that Material column and based on this I could extract the Material Weight. I created this LIST so that it could be expanded based on more matching values.

Search
mil
mm
gsm
gr

 

Now with these 2 things (1 table and 1 list), I want to create a function that takes this LIST as an input and search the values of this LIST in each cell of the Material column and return the exact material weight like below.

Job IdMaterialsResult
12.95 and 2.64 mm M.S wire2.64 mm
23mm PVC3 mm
8135 grs hv silk mc135 gr
10paper 120 gsm120 gsm
12PS 2mm2 mm
14kreda mat250gsm250 gsm
14Material: 70gsm FSC woodfree paper70 gsm
16115gsm Silk115gsm
17matte coated paper 250GSM250 gsm
18mat kuse135gr135 gr
2380 gsm Ozone fsc mixed credit80 gsm
24Cartulina gráfica 400 gr400 gr
25Vinil branco blackout 0.10 mm0.10 mm
29SINTRA 3mm3 mm
310.6mm Gloss Vinyl Coated Magnetic0.6 mm
34200 gr/qm Bilderdruck matt200 gr

 

My view is that you should change your processes so you have structured data as your product input instead of free form text



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Sorry, I don't understand. I can't go back and suggest my organisation to correct 2005 on wards historic data.
What I can really do is to use this wonderful tool and this forum to do my best to get the job done.
Thanks

What I think you are asking is, "what is the M code to operate over 15 years of unstructured data and extract any type of size information". Personally, I doubt this is possible. If you had a manageable quantity of descriptions, you could create a manual mapping table in Excel, then join it back in power BI, but that would need an identical match of the entire column. You may be able to find the location of the numeric portion in the description and extract that, possibly even followed by the next few characters, but then how would you handle variations, such as S/M/L/XL?



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
ImkeF
Super User
Super User

Hi @iamprajot 

please paste the following code into the advanced editor and follow the steps:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PZHNUoMwFIVf5QzrDkIo/XGnzNhxgXbE6abTRRoCZkigBrDq2/gsvpg3hHF5z/1ycnJyPAZxsAhYuE3B2xIsXC1hDPKwwFVZGZwWx4ARkZC4P2TTvKE5TlLUtsfbB3qlGxgxreKIdhd+kRYxi1D3xsvOYl+AmXle0txYWXIYPrA0+gfdIueDtIrrW6zdAg9FhmvXlZWVEpO5Z1cuR5w6pKAMXlyTSJ6DhOjIp/QHQHfsitwjG4+gGXtJ76i9HUtI3kyZ8fzdtRJVL2DUJ3kIiqoGj7mEGbfDqFXLqYPfn0oJjmVER2enlJCDapXG2fJWdDhrLppuHBCFcYS5BLYlrHh8en25QzJrifuNKFxR2zvd9T3I5ksj80/Jed3KQfmqExeETbfevBvcK11KW9pRNK5TCnv6Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Job Id" = _t, Materials = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Id", Int64.Type}, {"Materials", type text}}),
    #"Search Terms" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wys3MUYrVAdK5YCq9GEoXKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Search = _t]),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #"Search Terms"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Search"}, {"Search"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Position", each Text.PositionOf(Text.Lower([Materials]),Text.Lower([Search]))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Position] <> -1)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Number", each Text.Range([Materials],0,[Position])),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom2",{{"Number", Text.Trim, type text}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Trimmed Text", {{"Number", each Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Trimmed Text1" = Table.TransformColumns(#"Extracted Text After Delimiter",{{"Number", each Text.Select(_, {"0".."9", ",", "."}), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Trimmed Text1",{"Number", "Search"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

 

You'll see that #24 is a problem. If you come up with a rule that works, let me know.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

iamprajot
Responsive Resident
Responsive Resident

Thanks a lot @ImkeF.

My decision to seek your help was right. It's working, now I can use this logic to modify and further extract the required information.

I thought that List.Transform and List.Accumulate will be used here and I don't know how to use them.

If that's not too much then the only thing I need from you is if you could tell me how can I convert this to a function as I am using a list of values and there is no parameter used in this to convert this into a function.

 

Good to hear that this helped.

I believe there should be some tutorials about the function conversion on the www meanwhile.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

For the 24th item where the occurrence of the search string could be multiple.
I will sort this out and let you know.
I am thinking of two ways, I check if the 0..9 is returning any number of not and in case if there is no value then I will remove/replace the "gr" from Material and repeat the steps again to look for the next "gr". That is why I need a function that I could repeat again and again until I get the number.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.