Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
vypox
Frequent Visitor

Replace wildcard (?) with all numerical possibilities

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.

 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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:

BA_Pete_0-1697469036154.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

6 REPLIES 6
slorin
Super User
Super User

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
Super User
Super User

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:

BA_Pete_0-1697469036154.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




vypox
Frequent Visitor

Thanks! This works perfect for my specific case. 

 

No problem, happy to help.

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


Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




AlienSx
Super User
Super User

@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
Super User
Super User

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/ 
 

PBI_SuperUser_Rank@1x.png  

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors