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.
Gents,
It's hard to explain my problem using words so I'll list a few examples here:
I have 2 tables:
1 holding the data:
TAG | DATA SHEET | BLOCK DIAGRAM |
BOB101 | AAA;BBB,CCC | AAA;BBB,CCC |
BOB102 | AAA;BBB,CCC | AAA;BBB,CCC |
BOB103 | AAA;BBB,CCC | AAA;BBB,CCC |
1 holding the rules for text replacement
Attribute | Old Text | New Text |
DATA SHEET | ; | | |
DATA SHEET | , | | |
BLOCK DIAGRAM | ; | | |
BLOCK DIAGRAM | , | | |
I'm after the results below:
TAG | DATA SHEET | BLOCK DIAGRAM |
BOB101 | AAA|BBB|CCC | AAA|BBB|CCC |
BOB102 | AAA|BBB|CCC | AAA|BBB|CCC |
BOB103 | AAA|BBB|CCC | AAA|BBB|CCC |
The dataset is huge and so is the set of rules, they're on 2 separate excel files.
I'm after a dynamic approach where I can simply add lines to the rules file and see the changes after a refresh.
Solved! Go to Solution.
Changed the whole approach to a function. Many thanks @vanessafvg
I had hoped that a merge would do the trick. It works perfectly for a full replacement of a value but this one's trickier.
(InputAttr,InputValue)=> let SubTable = Table.SelectRows(REPLACE_TABLE,each [ATTR] = InputAttr), DoReplacement = List.Generate( ()=> [Counter=0, MyAttr=InputAttr, MyText=InputValue], each [Counter]<=List.Count(SubTable[OLD VAL]), each [Counter=[Counter]+1, MyText=Text.Replace( [MyText], SubTable[OLD VAL]{[Counter]}, SubTable[NEW VAL]{[Counter]})], each [MyText]), GetLastValue = List.Last(DoReplacement) in GetLastValue
Used this function to add a column; using the GetLastValue of the function as a Value for each row.
InputAttr is the Attribute/Field Name (after pivoting the table).
InputValue is the text that needs to be partially replaced.
List.Generate iterates through the text changes (in order of the RULES_TABLE).
Days of trial and error. not sure if I fully understand what I did there. Steps below:
REPLACE_TABLE
ATTR | OLD VAL | NEW VAL |
DATA SHEET | ; | | |
DATA SHEET | , | | |
BLOCK DIAGRAM | ; | | |
BLOCK DIAGRAM | , | | |
DATA_TABLE
TAG | DATA SHEET | BLOCK DIAGRAM |
BOB101 | AAA;BBB,CCC | AAA;BBB,CCC |
BOB102 | AAA;BBB,CCC | AAA;BBB,CCC |
BOB103 | AAA;BBB,CCC | AAA;BBB,CCC |
DATA_TABLE(UNPIVOT)
TAG | InputAttr | InputValue |
BOB101 | DATA SHEET | AAA;BBB,CCC |
BOB101 | BLOCK DIAGRAM | AAA;BBB,CCC |
BOB102 | DATA SHEET | AAA;BBB,CCC |
BOB102 | BLOCK DIAGRAM | AAA;BBB,CCC |
BOB103 | DATA SHEET | AAA;BBB,CCC |
BOB103 | BLOCK DIAGRAM | AAA;BBB,CCC |
DATA_TABLE(Add Column with Function Above)
TAG | InputAttr | InputValue | Changed Text |
BOB101 | DATA SHEET | AAA;BBB,CCC | AAA|BBB|CCC |
BOB101 | BLOCK DIAGRAM | AAA;BBB,CCC | AAA|BBB|CCC |
BOB102 | DATA SHEET | AAA;BBB,CCC | AAA|BBB|CCC |
BOB102 | BLOCK DIAGRAM | AAA;BBB,CCC | AAA|BBB|CCC |
BOB103 | DATA SHEET | AAA;BBB,CCC | AAA|BBB|CCC |
BOB103 | BLOCK DIAGRAM | AAA;BBB,CCC | AAA|BBB|CCC |
Straightforward from there; delete old column and re-pivot using unique TAG and InputAttr.
@WALEED why dont you just do a replace in powerquery (M)?
right click on the column in the query editor and select replace values?
Proud to be a Super User!
That would be ideal for a simple dataset.
My problem is that the list of rules is ever changing, adding/removing as new problems arise.
And the number of columns I have is 243 (can grow wider in the future)
The rules excel sheet is stored in a shared area where the team can add rows without opening the super heavy PBI file.
@WALEED sounds like you need to create a function then and pass parameters?
https://blog.learningtree.com/creating-functions-m-power-query-formula-language/
https://www.mattmasson.com/2014/11/converting-a-query-to-a-function-in-power-query/
Proud to be a Super User!
If only I could cook up the code to feed the function 😄 please help!
@WALEED email me ill give it a bash vanessafvg@gmail.com
Proud to be a Super User!
1. I've unpivoted the data table. All column names are now under "Attribute", and all values are under "Value"
2. I think I'm close. The formula is valid but the results are unchanged:
Custom1 = Table.ReplaceValue(LastStep, each if [Attribute] = RULE_TABLE[Attribute] then RULE_TABLE[OLD VAL] else RULE_TABLE[NEW VAL], each RULE_TABLE[NEW VAL], Replacer.ReplaceText, {"Value"})
Maybe I'm misplacing the "each"?
Changed the whole approach to a function. Many thanks @vanessafvg
I had hoped that a merge would do the trick. It works perfectly for a full replacement of a value but this one's trickier.
(InputAttr,InputValue)=> let SubTable = Table.SelectRows(REPLACE_TABLE,each [ATTR] = InputAttr), DoReplacement = List.Generate( ()=> [Counter=0, MyAttr=InputAttr, MyText=InputValue], each [Counter]<=List.Count(SubTable[OLD VAL]), each [Counter=[Counter]+1, MyText=Text.Replace( [MyText], SubTable[OLD VAL]{[Counter]}, SubTable[NEW VAL]{[Counter]})], each [MyText]), GetLastValue = List.Last(DoReplacement) in GetLastValue
Used this function to add a column; using the GetLastValue of the function as a Value for each row.
InputAttr is the Attribute/Field Name (after pivoting the table).
InputValue is the text that needs to be partially replaced.
List.Generate iterates through the text changes (in order of the RULES_TABLE).
Days of trial and error. not sure if I fully understand what I did there. Steps below:
REPLACE_TABLE
ATTR | OLD VAL | NEW VAL |
DATA SHEET | ; | | |
DATA SHEET | , | | |
BLOCK DIAGRAM | ; | | |
BLOCK DIAGRAM | , | | |
DATA_TABLE
TAG | DATA SHEET | BLOCK DIAGRAM |
BOB101 | AAA;BBB,CCC | AAA;BBB,CCC |
BOB102 | AAA;BBB,CCC | AAA;BBB,CCC |
BOB103 | AAA;BBB,CCC | AAA;BBB,CCC |
DATA_TABLE(UNPIVOT)
TAG | InputAttr | InputValue |
BOB101 | DATA SHEET | AAA;BBB,CCC |
BOB101 | BLOCK DIAGRAM | AAA;BBB,CCC |
BOB102 | DATA SHEET | AAA;BBB,CCC |
BOB102 | BLOCK DIAGRAM | AAA;BBB,CCC |
BOB103 | DATA SHEET | AAA;BBB,CCC |
BOB103 | BLOCK DIAGRAM | AAA;BBB,CCC |
DATA_TABLE(Add Column with Function Above)
TAG | InputAttr | InputValue | Changed Text |
BOB101 | DATA SHEET | AAA;BBB,CCC | AAA|BBB|CCC |
BOB101 | BLOCK DIAGRAM | AAA;BBB,CCC | AAA|BBB|CCC |
BOB102 | DATA SHEET | AAA;BBB,CCC | AAA|BBB|CCC |
BOB102 | BLOCK DIAGRAM | AAA;BBB,CCC | AAA|BBB|CCC |
BOB103 | DATA SHEET | AAA;BBB,CCC | AAA|BBB|CCC |
BOB103 | BLOCK DIAGRAM | AAA;BBB,CCC | AAA|BBB|CCC |
Straightforward from there; delete old column and re-pivot using unique TAG and InputAttr.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |