using M function, If text column CONTAINS specific value, then ...

[pureswing]

Hi everyone, I've been learnig a lot about m functions and how to handle queries, cool stuff indeed  🙂 

But my knowledge does not solve this problem and I'm stuck, I would appreciate any help please,

A Sample of the query (All Columns are Text Type😞

ID	Name	Description	Solution Expected
655123	Martha Ramos	3 Samples (555658)	3 Samples (Gray)
555123	Alexander Bay	Black and White	Black and White
12235	Unknown	7 Samples	7 Samples
555658	Mark Wall	Gray	Gray
1000058	Carlucho	10 Samples (555658)	10 Samples (Gray)
100356	Rodney Sarabia	3 Samples (555658) | 1 Sample (555123)	3 Samples (Gray) | 1 Sample (Black and White)

So I need to search for any number inside ( )  under the Description column compare this number to a number in ID column and if this number match then replaces this number with the corresponding description, look the Soluction Expected column for a better understending.

Thanks in advance guy 🙂 and Merry Christmas all!!

[MarcelBeug · Accepted solution]

Well, that's a nice Christmas puzzle, thanks!

Can't wait though:

let
    Source = Table1,

    // Add Index as original sort so the result can be sorted back to the original sort:
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),

    // Split text by parentheses, so the part numbers will be the 2nd, 4th, 6th item in the nested lists:
    #"Splitted Text" = Table.AddColumn(#"Added Index","Parts", each Text.SplitAny([Description],"()"), type {text}),

    // Take alternate items (only the parts that were between parentheses:
    #"Alternate Items" = Table.TransformColumns(#"Splitted Text",{{"Parts", each List.Alternate(_,1,1,0), type {text}}}),

    // Expand the list column to new rows
    #"Expanded Parts" = Table.ExpandListColumn(#"Alternate Items", "Parts"),

    // Merge table with itself:
    #"Merged Queries" = Table.NestedJoin(#"Expanded Parts",{"Parts"},Table1,{"ID"},"Table1",JoinKind.LeftOuter),

    // Expand the Description, which wil be the replacements values:
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Description"}, {"Table1.Description"}),

    // Add parentheses to ensure that only parts between parentheses will be replaced:
    #"Added Parentheses" = Table.TransformColumns(#"Expanded Table1",{{"Parts", each "("&_&")", type text}, {"Table1.Description", each "("&_&")", type text}}),

    // Group to the original number of rows, with "Parts" and "Table1.Description" combined to a list of lists for replacements which looks like: {{old, new},{old, new}, etc.}:
    #"Grouped Rows" = Table.Group(#"Added Parentheses", {"ID", "Name", "Description", "Index"}, {{"Replacements", each Table.ToRows(Table.SelectColumns(Table.SelectRows(_,each [Table1.Description] <> null),{"Parts","Table1.Description"})), type list}}),

    // Replacements are done using List.Accumulate to loop over each item in [Replacements] (r) and replace old (r{0}) with new (r{1}) in [Description] (d):
    #"Added Solution Expected" = Table.AddColumn(#"Grouped Rows", "Solution Expected", each List.Accumulate([Replacements],[Description], (d,r) => Replacer.ReplaceText(d, r{0}, r{1}))),

    // Back to original sort:
    #"Sorted Rows" = Table.Sort(#"Added Solution Expected",{{"Index", Order.Ascending}}),

    // Remove temporary columns:
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Replacements"})
in
    #"Removed Columns"

You might be interested in my Power Query (M) Functions Dashboard.

Happy holidays!

[MarcelBeug · Accepted solution]

Well, that's a nice Christmas puzzle, thanks!

Can't wait though:

let
    Source = Table1,

    // Add Index as original sort so the result can be sorted back to the original sort:
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),

    // Split text by parentheses, so the part numbers will be the 2nd, 4th, 6th item in the nested lists:
    #"Splitted Text" = Table.AddColumn(#"Added Index","Parts", each Text.SplitAny([Description],"()"), type {text}),

    // Take alternate items (only the parts that were between parentheses:
    #"Alternate Items" = Table.TransformColumns(#"Splitted Text",{{"Parts", each List.Alternate(_,1,1,0), type {text}}}),

    // Expand the list column to new rows
    #"Expanded Parts" = Table.ExpandListColumn(#"Alternate Items", "Parts"),

    // Merge table with itself:
    #"Merged Queries" = Table.NestedJoin(#"Expanded Parts",{"Parts"},Table1,{"ID"},"Table1",JoinKind.LeftOuter),

    // Expand the Description, which wil be the replacements values:
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Description"}, {"Table1.Description"}),

    // Add parentheses to ensure that only parts between parentheses will be replaced:
    #"Added Parentheses" = Table.TransformColumns(#"Expanded Table1",{{"Parts", each "("&_&")", type text}, {"Table1.Description", each "("&_&")", type text}}),

    // Group to the original number of rows, with "Parts" and "Table1.Description" combined to a list of lists for replacements which looks like: {{old, new},{old, new}, etc.}:
    #"Grouped Rows" = Table.Group(#"Added Parentheses", {"ID", "Name", "Description", "Index"}, {{"Replacements", each Table.ToRows(Table.SelectColumns(Table.SelectRows(_,each [Table1.Description] <> null),{"Parts","Table1.Description"})), type list}}),

    // Replacements are done using List.Accumulate to loop over each item in [Replacements] (r) and replace old (r{0}) with new (r{1}) in [Description] (d):
    #"Added Solution Expected" = Table.AddColumn(#"Grouped Rows", "Solution Expected", each List.Accumulate([Replacements],[Description], (d,r) => Replacer.ReplaceText(d, r{0}, r{1}))),

    // Back to original sort:
    #"Sorted Rows" = Table.Sort(#"Added Solution Expected",{{"Index", Order.Ascending}}),

    // Remove temporary columns:
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Replacements"})
in
    #"Removed Columns"

You might be interested in my Power Query (M) Functions Dashboard.

Happy holidays!

[pureswing]

Thank you Marcel you're very kind. Well I followed your steps using the same dummy table before jumping on my real data (a json file btw) but I found an error when I tried to merge the table with itself, precisely this error  Expression.Error: A cyclic reference was encountered during evaluation. So I did a few changes resulting in this steps:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
   #"Splitted Text" = Table.AddColumn(#"Added Index","Parts", each Text.SplitAny([Description],"()"), type {text}),
   #"Alternate Items" = Table.TransformColumns(#"Splitted Text",{{"Parts", each List.Alternate(_,1,1,0), type {text}}}),
   #"Expanded Parts" = Table.ExpandListColumn(#"Alternate Items", "Parts"),
   #"Merged Queries1" = Table.NestedJoin(#"Expanded Parts",{"ID"},#"Expanded Parts",{"ID"},"NewColumn",JoinKind.LeftOuter),
   #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"Description"}, {"NewColumn.Description"}),
   #"Added Parentheses" = Table.TransformColumns(#"Expanded NewColumn",{{"Parts", each "("&_&")", type text}, {"NewColumn.Description", each "("&_&")", type text}}),
   #"Grouped Rows" = Table.Group(#"Added Parentheses", {"ID", "Name", "Description", "Index"}, {{"Replacements", each Table.ToRows(Table.SelectColumns(Table.SelectRows(_,each [NewColumn.Description] <> null),{"Parts","NewColumn.Description"})), type list}}),
   #"Added Solution Expected" = Table.AddColumn(#"Grouped Rows", "Solution Expected", each List.Accumulate([Replacements],[Description], (d,r) => Replacer.ReplaceText(d, r{0}, r{1}))),
   #"Sorted Rows" = Table.Sort(#"Added Solution Expected",{{"Index", Order.Ascending}}),
   #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Replacements"})
in
   #"Removed Columns"

Resulting this:

ID	Name	Description	Solution Expected
655123	Martha Ramos	3 Samples (555658)	3 Samples (3 Samples (555658))
555123	Alexander Bay	Black and White	Error
12235	Unknown	7 Samples	Error
555658	Mark Wall	Gray	Error
1000058	Carlucho	10 Samples (555658)	10 Samples (10 Samples (555658))
100356	Rodney Sarabia	3 Samples (555658) | 1 Sample (555123)	3 Samples (3 Samples (3 Samples (555658) | 1 Sample (3 Samples (555658) | 1 Sample (3 Samples (555658) | 1 Sample (555123)))) | 1 Sample (3 Samples (555658) | 1 Sample (3 Samples (555658) | 1 Sample (555123)))) | 1 Sample (3 Samples (555658) | 1 Sample (3 Samples (555658) | 1 Sample (555123)))

Can you tell what I did wrong sir, please.

Regards.

[MarcelBeug · Accepted solution]

It's partly my fault. I stated that I was merging the table with itself, but I changed my mind - and the code - to merge the current query with Table1, which was another query that only imported (and typed) the table from Excel into Power Query.

Probably you named your query "Table1", that's why you got the cyclic reference error, as you can't refer to a query from inside the same query.
In case of 1 query, you can still reference the Source step as original table.

So, the only correction required in your query is the #"Merged Queries1" step, which has 2 errors: the first column must be "Parts", not "ID" and the second table must be Source, as explained above.

   #"Merged Queries1" = Table.NestedJoin(#"Expanded Parts",{"Parts"},Source,{"ID"},"NewColumn",JoinKind.LeftOuter),

[pureswing]

Marcel, what can I say??...you're a bless my friend 🙂 

I followed your suggestion and the result was brilliant!! thank again. 

But working with the json file I found that some rows did not change and after checking carefully the json file noticed the problem comes out when this characters are presents "\n" wich mean "new line" in javascrip lenguage, so now I have to find a fix around this converting
From this:

Order:
Room: 25% (500360)
Room: 25% (500370)
Room: 25% (500380)
Room: 25% (500390)

To this:

Order:Room: 25% (500360)Room: 25% (500370)Room: 25% (500380)Room: 25% (500390)

And well I will try to solve this by myself, since it does not seem complicated just that I do not have time today.

With nothing more to say. I wish a beautiful day to you and your family   Cheers!