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.
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.
Solved! Go to 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]
)
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"
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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
thank you for both option in Power Query and in DAX!
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"
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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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]
)
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |