Forum Discussion
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. |
Hi Anonymous ,
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 remOthCols2Example of output:
Pete
6 Replies
- BA_Pete
Super User
Hi Anonymous ,
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 remOthCols2Example of output:
Pete
- BA_Pete
No problem, happy to help.
Don't forget to give a thumbs-up on any posts that have helped you 👍
Pete
- Anand24
Hi Anonymous ,
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 - AlienSx
Anonymous
- 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" ?
slorinHi
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
ReplaceRangeStéphane
