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 have a column that contains a series of numbers that run from 1 digit to 9 digit numbers. Based off the number of digits, I need to modify the number and then prepend some text.
Example:
1 needs to transform to 0001 and then needs to have ABC prepended. So, the result is ABC0001
22 needs to transform to 0022 and then needs to have ABC prepended. So, the result is ABC0022
333 needs to transform to 0333 and then needs to have ABC prepended. So, the result is ABC0333
4444 needs to transform to 4444 and then needs to have ABC prepended. So, the result is ABC4444
etc all the way to 999999999
Is there an easy way to determine the number of digits and then transform and prepend?
Solved! Go to Solution.
Hi @Anonymous
Go to edit queries,
1. Add column->Format->Add Preifx, enter 00000000
2. click on "Prefix" column, Add column->Extact->last characters, enter count = 4
3. click on"Last Characters" column, Add column->Extact->first characters, enter count = 1
4.Add column->add conditioanl column
5. click on "number" column, Add column->Extact->length
6.click on "condition1" and "Last Characters" columns, Add column->merge columns
7.add conditional column
Finally, you could remove other columns, only keep "number" and "condition2" column.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
Best Regards
Maggie
Hi @Anonymous
Go to edit queries,
1. Add column->Format->Add Preifx, enter 00000000
2. click on "Prefix" column, Add column->Extact->last characters, enter count = 4
3. click on"Last Characters" column, Add column->Extact->first characters, enter count = 1
4.Add column->add conditioanl column
5. click on "number" column, Add column->Extact->length
6.click on "condition1" and "Last Characters" columns, Add column->merge columns
7.add conditional column
Finally, you could remove other columns, only keep "number" and "condition2" column.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Try to create a calculated column using the following Expression. You might get an error, but check the datatype of the column.
CalculatedColumn = "ABC" & REPT("0", 10-LEN(Table[Columnseriesofnumbers])) & Table[Columnseriesofnumbers]
Hope this will resolve your issue.
Thanks,
@suhasakole Thank you! The issue is more complex. I need to determine length, then add specific number of preceeding "0", or in the case of a 9 digit number, add nothing. The other bit of fun, if the modified string of numbers is less than 9 characters to begin with, I need to prepend ABC. If however the string if nubmbers was 9 to begin with, I need to add DEF.
Fun.
So I'm really doing 2 transformations. First, add in the preceeding "0" then based off that output final output, prepend the ABC or DEF.
Hi @Anonymous
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
Best Regards
Maggie
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 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |