Dealing with different dimension formats

[Applicable88]

Hello,

I looking for the best approach to deal with a sample table like this:

Storagetype		ShelvLocation
Standard		01-01-01/1
Standard		01-01-01/2
Standard		01-01-02/1
Standard		01-01-02/1
Standard		SP 1234
Standard		SP 1235
EXPO		001-01-01
EXPO		001-01-02
EXPO		001-01-03
EXPO		001-01-04
EXTRA		ET 1500
EXTRA		ET 1501

For examle Storagetype "Standard" has StorageLocations which can have a format like this "01-01-01" but also like this "SP 1234". Unfortunately some shelves which has the first format got misused because of capacity problems, so the employees squeeze two different Materials into the same location and rename it to ""01-01-01/01" and "01-01-01/02". Contentwise its not right, its still only one shelf not two, in case I want to count and calculate the capacity of the storage.

How to get rid of the suffix /01 and /02 in all shelves without changing all the other shelves with different formats?

Thank you very much in advance. 

Best. 

[tamerj1 · Accepted solution]

Hi @Applicable88 
Best to do that using power query. However, since you are posting the question in the DAX forum then I guess you're looking for a DAX solution. Please refer to attached sample file with the solution

ShelvLocation2 = 
IFERROR (   
    LEFT ( 'Table'[ShelvLocation], SEARCH ( "/", 'Table'[ShelvLocation], 1 ) - 1 ),
    'Table'[ShelvLocation]
)

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JzEtJLEpR0lEyMNQFI31DpVgd7DJGOGSMcOrBIhMcoGBoZGyCXdgULOwaEeAPMgNmMTZBI2yCxtgETaCCIUGOQFHXEAVDUwMDLGJAa2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Storagetype = _t, ShelvLocation = _t]),

    #"Removed text" = Table.TransformColumns(Source, {"ShelvLocation", each let pos = Text.PositionOf(_, "/") in if pos<>-1 then Text.RemoveRange(_, pos, Text.Length(_)-pos) else _})
in
    #"Removed text"

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JzEtJLEpR0lEyMNQFI31DpVgd7DJGOGSMcOrBIhMcoGBoZGyCXdgULOwaEeAPMgNmMTZBI2yCxtgETaCCIUGOQFHXEAVDUwMDLGJAa2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Storagetype = _t, ShelvLocation = _t]),

    #"Removed text" = Table.TransformColumns(Source, {"ShelvLocation", each let pos = Text.PositionOf(_, "/") in if pos<>-1 then Text.RemoveRange(_, pos, Text.Length(_)-pos) else _})
in
    #"Removed text"

[tamerj1 · Accepted solution]

Hi @Applicable88 
Best to do that using power query. However, since you are posting the question in the DAX forum then I guess you're looking for a DAX solution. Please refer to attached sample file with the solution

ShelvLocation2 = 
IFERROR (   
    LEFT ( 'Table'[ShelvLocation], SEARCH ( "/", 'Table'[ShelvLocation], 1 ) - 1 ),
    'Table'[ShelvLocation]
)

[Applicable88 · Accepted solution]

@CNENFRNL @tamerj1 ,

thank you for both option in Power Query and in DAX!