Specific formatting with DAX or M

[MarcoFogale]

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]

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]

@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.

[MarcoFogale]

@AlexisOlson that's correct.

For exemple:

input  (whole number) -> 2411000012312

expected output (text) -> 2411.0000.123.12

I was wondering if instead of a complicated DAX formula there was a way to obtain that with custom formatting strings, for example ####.####.###.## something like this...

Thank you 

[VahidDM · Accepted solution]

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/

[AlexisOlson]

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

[ValtteriN]

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!