Sorting a range of number and letters

[carlosagarcia]

Good morning everyone, 

I am looking for a way to divide inventory locations into zones.

Our inventory locations start with row 0, shelf A, bin 1, example 0A1. each shelf has 5 bins, the shelfs go from A to J and we have 70 rows.

0A5  0B5

0A4  0B4

0A3  0B3  and so on until shelf J

0A2  0B2

0A1  0B1

Then

1A5  1B5

1A4  1B4

1A3  1B3  and so on until shelf J

1A2  1B2

1A1  1B1

When I sort by location, the result is row 0 first then I get all the 10s, 11s and so on until I get row 1, then all the 20s before I get to row 2.

I am looking for a way to create inventory zones where...

Zone 1 are rows  everything in between 0A1 to 6J5

Zone 2 are rows everything in between 7A1 to 11J5

Zone 3 are rows everything in between 12A1 to 15J5 and so on.

Will this be possible?

[v-yiruan-msft]

Hi @carlosagarcia ,

In order to give you a suitable solution faster, could you please provide some sample data(exclude sensitive data) and the final result you want? And give examples of the sorting rules involved. If possible, a sample pbix file would be better. Thank you.

Best Regards

[tamerj1]

Hi @carlosagarcia 
It is doable. Please confirm on which order you need the result to be.

Your zones end 15J5, what about the rest? 
Please share sample file it available.

[tamerj1]

Hi @carlosagarcia 
Here is a sample file with the proposed solution https://www.dropbox.com/t/kdaoTHJT66w4n9vH
In the sample file I used DX code to generate a separate rows (Row, Shelf & Bin) but you can conveniently use Power Query (Add Column - From Example ) to do the same. Once the columns are seperate you can use DAX to create a new "Sorting by column" 

Sort By Number = 
VAR Shelves = 
    SWITCH ( TRUE, [Shelf] = "A", 1, [Shelf] = "B", 2, [Shelf] = "C", 3, [Shelf] = "D", 4, [Shelf] = "E", 5, [Shelf] = "F", 6, [Shelf] = "G", 7, [Shelf] = "H", 8, [Shelf] = "I", 9, [Shelf] = "J", 10 )
VAR Result =
    [Row] + Shelves * 100 + [Bin] * 10000
RETURN
    Result

Last step: select the location and from Column Tools - Sort By Column select the new column.


Please let me know if you need to create hierarchy and group by Zones. Thank you!

[tamerj1 · Accepted solution]

Hello @carlosagarcia 

have you had the chance to review my solution? Thank you

[carlosagarcia]

Tamerj1,

I am so sorry I did not see your response, I just did a quick reply, let me test it right now.

[amitchandak]

@carlosagarcia , Try a new column like

first concert you column to 4 digit

column = format([Your column], "0000")

then

Switch( True() ,

[column] >= "00A1" &&  [column]  <= "06J5", "Zone 1" ,

[column] >= "07A1" &&  [column]  <= "11J5", "Zone 2" ,

[column] >= "12A1" &&  [column]  <= "15J5", "Zone 3" )

[carlosagarcia]

Hello Ami, have you been able to review my question?

[carlosagarcia]

Hello Ami,

I cant get the new column to have 4 digits.

Looks like it is formated to text.

So the second part does not pick up the changes