Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi
I have a column that descibes a part.
It is of format: SSDR,480G,2A,IT276,27.8,EXAMP,LU
I am particularly interested in obtaining the size which is 480G.
The problem is that there are other variants in this:
Example: SSDR,3.8T,2A,IT27,8.9,X,99,AA (in thsi example, I want to extract 3.8T)
SSDR,NMMVV,3.84T,X.2,EXAMPL(in this particualr example, the size i want = 3.84T is the 3rd parameter in the split string)
SSDR,1.6TB,NMMVV,X.2,XX1 (in this example, I want 1.6TB)
The other problem that arises is that this column doesn't have all values starting with SSDR.
We have values like this ARRY,ABC,ABCDE22,EXL,X99,T500 but since this does not start with SSDR, we can leave this blank in the new column we create.
If your intended result is always after the first "delimiter" (i.e. after the first comma) then you can consider using a combination of the LEFT and FIND functions. However, if it is randomly located, it might be difficult to write a logical formula unless you want to make it a bit more complex; e.g. something that will allow the logic to pull "return value after first delimiter and if value after delimiter is NMMVV"
Proud to be a Super User! | |
@anushkavp27 This will get you the cases you've specified except having the size in the 3rd parameter. If you have a list of codes you need to search for you could modify this to take that into account.
Column =
IF(
LEFT([Value], 5) <> "SSDR,",
BLANK(),
VAR __NextComma = FIND(",", [Value], 6, -1)
VAR __Result = IF( __NextComma = -1, BLANK(), MID([Value], 6, __NextComma - 5 - 1) )
RETURN
__Result
)
Thank you for your response. I really appreciate it.
However, I also need to take care of the other cases, where the size is after the second comma in the string and they are a lot in number and hence, I have to account for them. And that is what makes this so complex 🙂
Hi @anushkavp27 ,
We need a common factor to identify the size. As an example, Starting with a letter or end with a letter or always in a specific location.
Did you notice any common factor for size in your dataset?
Thanks
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
85 | |
65 | |
51 | |
45 |
User | Count |
---|---|
217 | |
88 | |
81 | |
65 | |
56 |