Extract measurements between texts or specific delimiter

[Vinicsb]

Hello,

I am facing the following problem. I need to extract measurements of varied products, with different standards.
In this case, I need to sort by columns like width, height and length.

Below is a table to exemplify the data. Note that in some cases the product is composed of numbers (Tee 90), and may affect extraction.

PRODUCT	WIDTH	HEIGHT	LENGTH
Cable Tray 100X75X3000 #26	100	75	30000
Profiled 38X38X6000 #18	38	38	6000
Cable Tray Cover 100X3000 #24	100		3000
Horizontal Bend 1200X200 #12	1200	200
Tee 90 50X50	50	50
Ladder Tray Cover 150	150
Vertical Bend Inside

[Nathaniel_C]

Hi @Vinicsb ,
This is one of the things that make Power Query so amazing!
My steps are as follows: (I add the columns that you wanted so that we can compare for the final view.)
Duplicate the column.

Split the column at the delimiter X at each occurrence.

Extract the text after the delimiter - hit the space bar for this - starting from the end of the value.

Extract the text before the delimiter - space - starting from the beginning of the value.

Extract the text from before the delimiter - space.

[Nathaniel_C · Accepted solution]

Hi @Vinicsb ,
Next select the first col and change type to whole number.  That way the text will generate an error.  Then replace the error with null.

Then for clarity change the name of the columns.

This took longer to get the pictures than the work.

You can paste this into Advanced Editor
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY5fC4IwFMW/ysVefZizqb2mooKUhJUgPlhbMBCFJUF9+u7mn4LLGZx7zv2trq3idIzOYWnZ1jWLyhTfNM6SVBt5fEjQaezaCttbJ6BU7RscQiqfVS4hBDbUwxw6qD5D0S4xjUIND9kJDm5Q4Xgm7gQ6s4q3hP/Oh8NLKAOZCduVADPAVNJByc/Qj20He9FzcChWqIFQ3aCmQueibpRCwI4AIxXT5k+mdd5yjuT/T5itwxb2lLsINcr7gs36p+QCl/M0zRc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"PRODUCT", type text}, {"WIDTH", Int64.Type}, {"HEIGHT", Int64.Type}, {"LENGTH", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "PRODUCT", "PRODUCT - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "PRODUCT", Splitter.SplitTextByDelimiter("X", QuoteStyle.Csv), {"PRODUCT.1", "PRODUCT.2", "PRODUCT.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"PRODUCT.1", type text}, {"PRODUCT.2", type text}, {"PRODUCT.3", type text}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type2", {{"PRODUCT.1", each Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"PRODUCT.2", each Text.BeforeDelimiter(_, " "), type text}}),
    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"PRODUCT.3", each Text.BeforeDelimiter(_, " "), type text}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter1",{{"PRODUCT.1", Int64.Type}, {"PRODUCT.2", Int64.Type}, {"PRODUCT.3", Int64.Type}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type3", {{"PRODUCT.1", null}}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Errors",{{"PRODUCT.1", "My Width"}, {"PRODUCT.2", "My Height"}, {"PRODUCT.3", "My Length"}, {"PRODUCT - Copy", "Product"}})
in
    #"Renamed Columns"

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Vinicsb]

@Nathaniel_C , Thank you very much, especially for taking the time to show it through step-by-step

[Nathaniel_C]

Hi @Vinicsb ,
You are welcome!

@KenPuls has written a great book, M is for (Data) Monkey, gives you lots of tips, tricks and patterns. That is where I gained an understanding about Power Query. Amazon carries it for around $30 US.

Nathaniel

[Nathaniel_C]

Hi @Vinicsb ,
Please give an example of what you wish to see as a final view.

Thank you,

Nathaniel

[Vinicsb]

Hi @Nathaniel_C, 
I would like to extract the measurements from the PRODUCT column. In this table I tried to demonstrate the example of how I would like the final result to look like. I tried many ways but I didn't succeed.