Replace Wildcard with List in PQ

[beagleton]

Hello,

A co-worker has presented me with a fairly unique ask, and I wasn't sure if there is a PowerQuery solution or not. As such, I figured its best to ask the guru.


I have two lists, one with names like:

unit *
system *
limit *
range *


The other list has values like

ABC
DEF
GHI
JKL

What I would like to try and do is replace the wildcard symbol in the first list with the entire second list. If done correctly, the output would be:

unit ABC
unit DEF
unit GHI
unit JKL
system ABC
system DEF
system GHI
system JKL
limit ABC
limit DEF
limit GHI
limit JKL
range ABC
range DEF
range GHI
range JKL

Does anyone know of a way to do this in PQ? My first instinct was to use Replace Values, but this wont allow a list to be input as the Replace With field. I figured Merge Queries would be helpful too, but although it seems like there should be some way to use this, I can't really think of one. I'm sure there must be an M solution. Any help is appreciated.
Thanks,
Brendan

 

[Ken Puls]

Bill will probably come along and post a one step deal to do it all, but it looks to me like you have a delimiter in a space. So you could split your first column by the space, the use a custom column to reference the other table and expand it. (I realize that you have lists here, but they can be converted to tables easily.)

Just a quick fix to get you going...

 

[Bob Phillips]

Try this

let
_other.list = Text.Combine(Table.ToList(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]), ","),
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
other.list.combine = Table.AddColumn(Source, "Custom", each _other.list),
other.list.split = Table.SplitColumn(other.list.combine, "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
other.list.unpivot = Table.UnpivotOtherColumns(other.list.split, {"Id"}, "Attribute", "Value"),
attribute.column.remove = Table.RemoveColumns(other.list.unpivot,{"Attribute"})
in
attribute.column.remove

 

[beagleton]

Marvelous. As always

 

[MarcelBeug]

Alternative solution that also works with values without wildcard.

As source data I created 2 tables:
"OneList" with column "Wildcard Values"
"AnotherList" with column "Value".

let
    Source = OneList,
    #"Added Custom" = Table.AddColumn(Source, "Value", each if Text.Contains([Wildcard Values],"*") then AnotherList[Value] else {""}),
    #"Expanded Value" = Table.ExpandListColumn(#"Added Custom", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Value", "Replaced Wildcards", each Replacer.ReplaceText([Wildcard Values],"*", [Value]), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Wildcard Values", "Value"})
in
    #"Removed Columns"

 

[Bob Phillips]

Alternative solution that also works with values without wildcard.

As source data I created 2 tables:
"OneList" with column "Wildcard Values"
"AnotherList" with column "Value".

let
    Source = OneList,
    #"Added Custom" = Table.AddColumn(Source, "Value", each if Text.Contains([Wildcard Values],"*") then AnotherList[Value] else {""}),
    #"Expanded Value" = Table.ExpandListColumn(#"Added Custom", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Value", "Replaced Wildcards", each Replacer.ReplaceText([Wildcard Values],"*", [Value]), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Wildcard Values", "Value"})
in
    #"Removed Columns"

That is an excellent solution IMV, the best offered :smile:

 

[Pecoflyer]

Coming from Bob, that is a extraordinary achievement !