Replace wildcard (?) with all numerical possibilities

[vypox]

I am struggling with translating wildcards. My dimension table only has numbers (not ?-wildcard). In DAX I do not know of any wildcard lookups, so I want to fix in Power Query the source data so I can create the join. 

Situation: 

Input can contain one or more ?. Expected output needs to be translated to all numerical possibilities (0-9). 

Input

5334182484

1234?49123

5283??4912

Output
5334182484
1234049123
1234149123
1234249123
1234349123
1234449123
1234549123
1234649123
1234849123
1234949123
5283004912
5283014912
5283024912
5283034912
5283044912
etc.

[slorin]

Hi

for fun, another solution with Text.ReplaceRange

let
Source = YourSource,
Position = Table.AddColumn(Source, "Position", each Text.PositionOf([Input], "?", Occurrence.All), type list),
Count = Table.AddColumn(Position, "Count", each List.Count([Position]), Int64.Type),
List = Table.AddColumn(Count, "List", 
     (x) => List.Transform({0..Number.Power(10, x[Count])-1}, 
         each Text.ToList(Text.PadStart(Text.From(_),x[Count],"0"))), type list),
Expand = Table.ExpandListColumn(List, "List"),
ReplaceRange = Table.AddColumn(Expand, "Output", 
     each List.Accumulate(List.Zip({[Position],[List]}),[Input], 
        (state,current) => if [Count] = 0 then state else Text.ReplaceRange(state,current{0},1,current{1})), type text)
in
ReplaceRange

Stéphane

[BA_Pete · Accepted solution]

Hi @vypox ,

Try this out. It's not clean (particularly expanding out each list column) but hopefully it gives you an idea of what's possible:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU2NjG0MDKxMFGK1YlWMjQyNrE3sQRSYK6pkYWxPZivFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    dupeInput = Table.DuplicateColumn(Source, "Input", "InputCopy"),
    splitByPos = Table.SplitColumn(dupeInput, "InputCopy", Splitter.SplitTextByPositions({0, 1, 2, 3, 4, 5, 6, 7, 8, 9}), List.Transform({1..10}, each Text.From(_))),
    unpivOtherCols = Table.UnpivotOtherColumns(splitByPos, {"Input"}, "Attribute", "Value"),
    addListed = Table.AddColumn(unpivOtherCols, "listed", each if [Value] = "?" then {"0".."9"} else {[Value]}),
    remOthCols1 = Table.SelectColumns(addListed,{"Input", "Attribute", "listed"}),
    pivotAttrib = Table.Pivot(remOthCols1, List.Distinct(remOthCols1[Attribute]), "Attribute", "listed"),
    expand1 = Table.ExpandListColumn(pivotAttrib, "1"),
    expand2 = Table.ExpandListColumn(expand1, "2"),
    expand3 = Table.ExpandListColumn(expand2, "3"),
    expand4 = Table.ExpandListColumn(expand3, "4"),
    expand5 = Table.ExpandListColumn(expand4, "5"),
    expand6 = Table.ExpandListColumn(expand5, "6"),
    expand7 = Table.ExpandListColumn(expand6, "7"),
    expand8 = Table.ExpandListColumn(expand7, "8"),
    expand9 = Table.ExpandListColumn(expand8, "9"),
    expand10 = Table.ExpandListColumn(expand9, "10"),
    addOutput = Table.AddColumn(expand10, "Output", each Text.Combine({[1], [2], [3], [4], [5], [6], [7], [8], [9], [10]}, ""), type text),
    remOthCols2 = Table.SelectColumns(addOutput,{"Input", "Output"})
in
    remOthCols2

Example of output:

Pete

[vypox]

Thanks! This works perfect for my specific case. 

[BA_Pete]

No problem, happy to help.

Don't forget to give a thumbs-up on any posts that have helped you ‌‌👍

Pete

[AlienSx]

@vypox 

 - what is max amount of ?? ? Or just "?" and "??" like in your sample?

 - do you have more than one occurrence of wildcard in the word? E.g. "123?456??78"

 - do you have leading or trailing wildcards? E.g. "??123456" ?

[Anand24]

Hi @vypox ,
As per my knowledge, this is not directly possible in Power Query editor or in Power BI itself. You are trying to tweak the values and increase no. of rows, hence I doubt it is possible in Power BI.

Maybe you can try out by manually putting from fields and playing around with Pivot and Unpivot. Again this would be a trial and error method which I;m not sure will fetch you your desired result.

Alternatively, you can raise a ticket with MS support and get a confirmation if this is even possible.
Link to MS Support: https://support.fabric.microsoft.com/en-IN/support/ 
 

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn