Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |