Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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,
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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |