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.
I need to replace a text in a specific column, but as my report comes from a cube, I cannot manipulate the data using Power Query or Add Columns, the only option I have is to create a measure using DAX.
I did a research and I could use Replace or Substitute, but I'm still not quite sure how to replace a text every time it appears in the column. Below is an example in excel of what I'm looking for to do in PBI. In this example, I need to substitute/replace Apple for Pear.
Original Names | NEW Names |
Apple | Pear |
Banana | Banana |
Strawberry | Strawberry |
Apple | Pear |
Banana | Banana |
Strawberry | Strawberry |
Strawberry | Strawberry |
Strawberry | Strawberry |
Banana | Banana |
Banana | Banana |
Apple | Pear |
Banana | Banana |
Apple | Pear |
Apple | Pear |
Strawberry | Strawberry |
Apple | Pear |
Banana | Banana |
Strawberry | Strawberry |
Strawberry | Strawberry |
Strawberry | Strawberry |
Banana | Banana |
Banana | Banana |
I appreciate any help. Thanks!
Solved! Go to Solution.
Whenever your source is live connection you can create only measure from power BI side.
For your requirement it's better replace values at cube side on existing column or by creating new column.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
Hello,
Yes, you could use the SUBSTITUTE function but it appears that it only allows one string at a time. If you're facing an issue where you need to replace the string value of multiple rows, I don't think this would work.
What I would do would be to use the SWITCH() and SELECTEDVALUE() functions. Unfortunately it's a very manual solution but if you don't have 100s of words to replace, this could be an effective solution without needing to change the underlying data values.
For example:
NEW Names =
VAR SelectedName = SELECTEDVALUE([Original Name]) //This returns the selected value from original name in the table visual.
RETURN
SWITCH( SelectedName, //This evaluates true if the 2nd argument is ==, else it evaluates false and continues through the switch
"Apple", "Pear",
"Raspberry", "Blueberry",
"Cherry", "Watermelon",
"SelectedName" //This simply returns the selected name if the name doesn't equal the values listed above
)
Thanks for the reply. I tried it but unfortunately, it didn't work. I guess I need to change in the cube.
Whenever your source is live connection you can create only measure from power BI side.
For your requirement it's better replace values at cube side on existing column or by creating new column.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
Thanks for the reply. I need to change it in the cube indeed.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |