Specific formatting with DAX or M

Hi,

I have a series of articles numbers that come from the database as an integer. The users are now used to data exported in Excel where these numbers appears with a particular format, for exemple: 1234.567.89.11

Where the number is smaller, it takes only the firsts dots - exemple: 1.22.33 or 12.234.56.78

I need to show them these numbers formatted this way.

How could I acheive it? I imagine I could do it with a complicated DAX formula, but I was wondering if there's another workaround. I haven't figured out yet how to make it with custom formatting string...

Thank in advance

Marco

VahidDM

4 years ago

Hi AlexisOlson

Thanks for letting me know, it makes sense now for me.

So MarcoFogale if you need DAX try this code to add a new calculated column:

New Format = 
Var _L = len('Table (2)'[article number])
Var _A = RIGHT('Table (2)'[article number],2)
Var _B = RIGHT(left('Table (2)'[article number],if(_L-2<=0,0,_L-2)),2)
Var _C = RIGHT(LEFT('Table (2)'[article number],if(_L-4<=0,0,_L-4)),3)
Var _D = RIGHT(LEFT('Table (2)'[article number],if(_L-7<=0,0,_L-7)),4)
Var _G = _D & "." & _C & "." & _B & "." & _A
Var _H = if(left(_G,1)=".",right(_G,len(_G)-1),_G)
return
_H

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

6 Replies

ValtteriN
Community Champion
4 years ago
Hi,

I believe the best way to do this is in powerquery. To do this you can do the following steps:

1. Split column with multiple comma values by delimiter. Select the right most comma as the splitpoint
2. Remove the commas from the left most column
3. Merge the two columns into one
4. Format data as decimal number

Example:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzNDLSMzXSMzNTitWJVjLRMzM31zM11TM3V4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Value", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Value.1", "Value.2"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",".","",Replacer.ReplaceText,{"Value.1"}),
#"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"Value.1", "Value.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type number}})
in
#"Changed Type"
End result:

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!
AlexisOlson
Super User
4 years ago
If you can define the formatting rules, then it's possible in either M or DAX. Which is more convenient depends on the specific rules but M has a richer set of functions for manipulating strings and lists.

It's not pretty but here's an example of a custom column for one possible set of rules:

let T = Number.ToText([Column1]), L = Text.Length(T), Result = if L > 7 then Text.Insert(Text.Insert(Text.Insert(T, L-2, "."), L-4, "."), L-7, ".") else if L > 4 then Text.Insert(Text.Insert(T, L-2, "."), L-4, ".") else if L > 2 then Text.Insert(T, L-2, ".") else T in Result
VahidDM
Super User
4 years ago
Hi MarcoFogale

Not sure I understood your question correctly or not (can you add a result), but if you want to remove dot from those article numbers with DAX, try this:

New Format = SUBSTITUTE([article number],".","")

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
- AlexisOlson
  Super User
  4 years ago
  VahidDM ValtteriN, OP specified that the numbers come in integer form, which doesn't have an inherent dot formatting like that. I think they want to insert dots into a raw integer rather than remove them from a text string.
  
  MarcoFogale Please correct me if I have misunderstood.
  - VahidDM
    Super User
    4 years ago
    Hi AlexisOlson
    
    Thanks for letting me know, it makes sense now for me.
    
    So MarcoFogale if you need DAX try this code to add a new calculated column:
    New Format = Var _L = len('Table (2)'[article number]) Var _A = RIGHT('Table (2)'[article number],2) Var _B = RIGHT(left('Table (2)'[article number],if(_L-2<=0,0,_L-2)),2) Var _C = RIGHT(LEFT('Table (2)'[article number],if(_L-4<=0,0,_L-4)),3) Var _D = RIGHT(LEFT('Table (2)'[article number],if(_L-7<=0,0,_L-7)),4) Var _G = _D & "." & _C & "." & _B & "." & _A Var _H = if(left(_G,1)=".",right(_G,len(_G)-1),_G) return _H
    
    output:
    
    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn: www.linkedin.com/in/vahid-dm/