PQ Replace Text With List

[cr731]

In PQ, is it possible to replace specific characters of a text string with a list of values? I would like to replace a wildcard ("*") with a list of possible values to output a list of all possible combinations. For instance, I have a 10-digit account number where a person can input wildcards, like so

12345678**
**12345678

Each of those * characters could be 0-9, so for each of the above lines with there being 2 wildcards, each line becomes 100 possible outcomes

1234567800
1234567801
...
1234567899

I cannot figure out how to do this type of replacement in Power Query but I assume it's possible. My initial thought was to split each line into a list of characters, then replace the * with a list of {0..9}. But it seems that Text.Split requires a delimiter, which I do not have.

Thanks

 

[billszysz]

You can use something like this below (with fxAccount function)

let    fxAccount = (txt as text) =>
        let
            fxComb = (t as table, l as list, n as number) as table =>
                let
                    Step1 = if n = List.Count(l) then "End" else Table.ExpandListColumn(t, l{n}),
                    Step2 = if Step1 = "End" then t else @fxComb(Step1, l, n+1)
                in
                    Step2,


            positions = List.Transform(Text.PositionOf(txt, "*", 2), each "Column" & Text.From(_+1)),
            tables = Table.FromRows({List.Transform(Text.ToList(txt), each if _ = "*" then {"0".."9"} else _)}),
            GetTable = fxComb(tables, positions, 0),
            AddCol = Table.AddColumn(GetTable, "Ready", each Text.Combine(Record.ToList(_))),
            RemOthCol = Table.SelectColumns(AddCol,{"Ready"})
        in
            RemOthCol,




    Source = Table.FromColumns({{"*123*567*9","0123456789","99*9999999"}}, {"RawData"}),
    #"Added Custom" = Table.AddColumn(Source, "ToExpand", each fxAccount([RawData])),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Custom", "ToExpand", {"Ready"}, {"Ready"})
in
    #"Expanded {0}"

Put this code in advanced editor and check the result.

 

[MarcelBeug]

Alternatively:

let
    fnReplaceWildCards = (MyString as text) as table =>
    let
        TextToList = Text.ToList(MyString),
        ReplacedWildCards = List.Transform(TextToList, each if _ = "*" then {{"0".."9"}} else {{_}}),
        TableFromColumns = Table.FromColumns(ReplacedWildCards),
        ExpandedListColumns = List.Accumulate(Table.ColumnNames(TableFromColumns),TableFromColumns,(Table,Column) => Table.ExpandListColumn(Table,Column)),
        CombinedColumns = Table.AddColumn(ExpandedListColumns, "Account number", each Text.Combine(Record.FieldValues(_))),
        #"Removed Other Columns" = Table.SelectColumns(CombinedColumns,{"Account number"})
    in
        #"Removed Other Columns",
    Source = Table1,
    ReplacedWildCards = Table.TransformColumns(Source,{{"Account number", fnReplaceWildCards, Value.Type(Source)}}),
    #"Expanded Account number" = Table.ExpandTableColumn(ReplacedWildCards, "Account number", {"Account number"})
in
    #"Expanded Account number"

 

[cr731]

Thanks! Could you explain what this line does? I can't quite get my head around this part:

        ExpandedListColumns = List.Accumulate(Table.ColumnNames(TableFromColumns),TableFromColumns,(Table,Column) => Table.ExpandListColumn(Table,Column)),

 

[MarcelBeug]

Well, all columns in the table from the previous step - TableFromColumns - are nested lists.
These are expanded one by one:

List.Accumulate takes a list as first argument (i.c. the column names from TableFromColumns), an initial value in the second argument (i.c. TableFromColumns) and then it loops over the elements in the first argument, performing the action as defined in the third element. This action changes the value from the second argument.
The third argument is a function with 2 arguments: the first refers to the second argument of List.Accumulate (the table) and the third refers to (each element of) the first argument of List.Accumulate (each column name).

I also explained List.Accumulate in a video in the series about solving 12x12 binary puzzles with Power Query.