cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Applicable88
Impactful Individual
Impactful Individual

Dealing with different dimension formats

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. 

 

 

3 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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

1.png

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

View solution in original post

CNENFRNL
Super User
Super User

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_0-1664734913584.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

@CNENFRNL @tamerj1 ,

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

 

View solution in original post

3 REPLIES 3
CNENFRNL
Super User
Super User

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_0-1664734913584.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

tamerj1
Super User
Super User

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

1.png

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

@CNENFRNL @tamerj1 ,

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

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors