Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Id | Materials |
1 | 2.95 and 2.64 mm M.S wire |
2 | 3mm PVC |
8 | 135 grs hv silk mc |
10 | paper 120 gsm |
12 | PS 2mm |
14 | kreda mat250gsm |
14 | Material: 70gsm FSC woodfree paper |
16 | 115gsm Silk |
17 | matte coated paper 250GSM |
18 | mat kuse135gr |
23 | 80 gsm Ozone fsc mixed credit |
24 | Cartulina gráfica 400 gr |
25 | Vinil branco blackout 0.10 mm |
29 | SINTRA 3mm |
31 | 0.6mm Gloss Vinyl Coated Magnetic |
34 | 200 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 Id | Materials | Result |
1 | 2.95 and 2.64 mm M.S wire | 2.64 mm |
2 | 3mm PVC | 3 mm |
8 | 135 grs hv silk mc | 135 gr |
10 | paper 120 gsm | 120 gsm |
12 | PS 2mm | 2 mm |
14 | kreda mat250gsm | 250 gsm |
14 | Material: 70gsm FSC woodfree paper | 70 gsm |
16 | 115gsm Silk | 115gsm |
17 | matte coated paper 250GSM | 250 gsm |
18 | mat kuse135gr | 135 gr |
23 | 80 gsm Ozone fsc mixed credit | 80 gsm |
24 | Cartulina gráfica 400 gr | 400 gr |
25 | Vinil branco blackout 0.10 mm | 0.10 mm |
29 | SINTRA 3mm | 3 mm |
31 | 0.6mm Gloss Vinyl Coated Magnetic | 0.6 mm |
34 | 200 gr/qm Bilderdruck matt | 200 gr |
Solved! Go to Solution.
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
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 Id | Materials |
1 | 2.95 and 2.64 mm M.S wire |
2 | 3mm PVC |
8 | 135 grs hv silk mc |
10 | paper 120 gsm |
12 | PS 2mm |
14 | kreda mat250gsm |
14 | Material: 70gsm FSC woodfree paper |
16 | 115gsm Silk |
17 | matte coated paper 250GSM |
18 | mat kuse135gr |
23 | 80 gsm Ozone fsc mixed credit |
24 | Cartulina gráfica 400 gr |
25 | Vinil branco blackout 0.10 mm |
29 | SINTRA 3mm |
31 | 0.6mm Gloss Vinyl Coated Magnetic |
34 | 200 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 Id | Materials | Result |
1 | 2.95 and 2.64 mm M.S wire | 2.64 mm |
2 | 3mm PVC | 3 mm |
8 | 135 grs hv silk mc | 135 gr |
10 | paper 120 gsm | 120 gsm |
12 | PS 2mm | 2 mm |
14 | kreda mat250gsm | 250 gsm |
14 | Material: 70gsm FSC woodfree paper | 70 gsm |
16 | 115gsm Silk | 115gsm |
17 | matte coated paper 250GSM | 250 gsm |
18 | mat kuse135gr | 135 gr |
23 | 80 gsm Ozone fsc mixed credit | 80 gsm |
24 | Cartulina gráfica 400 gr | 400 gr |
25 | Vinil branco blackout 0.10 mm | 0.10 mm |
29 | SINTRA 3mm | 3 mm |
31 | 0.6mm Gloss Vinyl Coated Magnetic | 0.6 mm |
34 | 200 gr/qm Bilderdruck matt | 200 gr |
My view is that you should change your processes so you have structured data as your product input instead of free form text
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?
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |