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.
Hope someone can help me on this whether in Power Query or DAX.
My data is Title and ID columns.
1st scenario - I need to create a new column that extracts the data from TITLE field that does not ends with ":X"
The column no ":X" is the expected output
2nd scenario - I need to create a new column that extracts the data from TITLE field that does not ends with value corresponding to ID field. Delimiter is ":". Sample is 2nd row below ID field contains "1234" so I have to remove ":1234" from Title field.
Expected output columns are no ":X" and no ":[ID]"
Title | ID | no ":X" | no ":[ID]" | |||
Alabama:Montgomery:1234 | 1234 | Alabama:Montgomery:1234 | Alabama:Montgomery | |||
Alaska:Juneau:X | Alaska:Juneau | Alaska:Juneau:X | ||||
Arizona:Phoenix:X | Arizona:Phoenix | Arizona:Phoenix:X | ||||
Arkansas:LittleRock:Test:X | Arkansas:LittleRock:Test | Arkansas:LittleRock:Test:X | ||||
California:Sacramento:est:3854 | 3854 | California:Sacramento:est:3854 | California:Sacramento:est | |||
Colorado:Denver:2021 | Colorado:Denver:2021 | Colorado:Denver:2021 | ||||
Connecticut:X:Hartford:AAA | Connecticut:X:Hartford:AAA | Connecticut:X:Hartford:AAA | ||||
Delaware:Dover:Type:7877 | 7877 | Delaware:Dover:Type:7877 | Delaware:Dover:Type |
For scenario 1, this formula works but it also extracts wherever my delimiter can be found. I only need to extract data if it ENDS with the delimiter
Solved! Go to Solution.
pls code in power query
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Hi @summer18
Try below DAX expressions ,
1.
2.
Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Thank you,
Glad to know !
@summer18
Could you please give a thumbs up and accept this as a solution as well, since you confirmed that DAX is working too?
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 |
---|---|
107 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
143 | |
104 | |
104 | |
80 | |
66 |