Double Sort or Sort into another previous Sort ? (into a M code)

[Anonymous]

Hi, community,
Recently I posted for some help and the community gave me this M code that properly works for what I asked;

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjOwNDCx0DU0sARiJR0lCyA2N1KK1cGUMjIEEobG2CWNjUGSBjgkTfDotDDDI2lpDiTMDJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Pallet ID" = _t, #"Pallet Pcs" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Pallet ID", Int64.Type}, {"Pallet Pcs", Int64.Type}}),
    #"Added Enough Pieces" = 
        Table.AddColumn(
            #"Changed Type", 
            "Enough Pieces", 
            each
                let 
                    varItemNeeded = [Item],
                    varPalletId = [Pallet ID],
                    varPiecesNeeded = Table.SelectRows(#"Table A", each [Item] = varItemNeeded)[Pcs to complete request]{0}
                in
                List.Sum(
                    Table.SelectRows(#"Changed Type", each [Pallet ID] <= varPalletId)[Pallet Pcs]
                ) > varPiecesNeeded,
                type logical
        ),
    #"Added Pallet Needed" = 
        Table.AddColumn(#"Added Enough Pieces", "Pallet Needed", 
        each 
            let
                varLastPallet = 
                    List.Min(
                        Table.SelectRows(#"Added Enough Pieces", each [Enough Pieces] = true)[Pallet ID]
                    )
            in
            [Pallet ID] <= varLastPallet,
            type logical
        ),
    #"Filtered Rows To Keep What Is Needed" = Table.SelectRows(#"Added Pallet Needed", each ([Pallet Needed] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows To Keep What Is Needed",{"Item", "Pallet ID", "Pallet Pcs"})
in
    #"Removed Other Columns"

But now, I need a little bit more help; How can I edit this M code to do a Sort into the previous Sort? 
Let me explain you with an example.

Table A;

tem	Request	OnSite Pcs	OffSite Pcs	Pcs to complete request
609048-109-10	120	20	160	100

Table B;

Item	Pallet ID	Pallet Pcs
609048-109-10	7	10
609048-109-10	10	40
609048-109-10	12	20
609048-109-10	13	20
609048-109-10	9	20
609048-109-10	11	40
609048-109-10	8	10

The M code will bring me the pallets 7, 8, 9, 10, and 11 because these pallets, in order to the sort by Pallet ID, sum 120 pcs to complete the 100 requested. Right?

But, Is possible to edit the M code to sort first by Pallet Pcs, and then, by Pallet ID? (Something like the next table).
Because if it's possible,  the M code could return me only the Pallets 10, 11, and 9, these pallets sum 100 pcs.

Item	Pallet ID	Pallet Pcs
609048-109-10	10	40
609048-109-10	11	40
609048-109-10	9	20
609048-109-10	12	20
609048-109-10	13	20
609048-109-10	7	10
609048-109-10	8	10

Is it possible?
I hope you can help me again community...
Have a good day!

***The blue color in the tables doesn't have any relation with the blue text of the Measure, it's just for can diference the columns.

[edhans]

Man, the person that did that M code is a genius! That is some beautiful M code!!!!! 😂😂😂 🤣🤣🤣

Try this code. I needed to sort the pallets and quantities due to the ties in quantity whereas last time I only cared about the first pallet to hit the quantity needed. The Pallet ID was my unique index. So I sorted by descending quantity and ascending Pallet ID, then added an index. Then tweaked a few other things. This should work though. The PBIX file is here. I didnt' bother redoing the DAX code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjOwNDCx0DU0sARiJR0lcyAGMmJ1MKXAhAkOOSMgYYRDzhi3nCUebYa4rbOAujIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Pallet ID" = _t, #"Pallet Pcs" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Pallet ID", Int64.Type}, {"Pallet Pcs", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Pallet Pcs", Order.Descending}, {"Pallet ID", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Enough Pieces" = 
        Table.AddColumn(
            #"Added Index", 
            "Enough Pieces", 
            each
                let 
                    varItemNeeded = [Item],
                    varIndex = [Index],
                    varPiecesNeeded = Table.SelectRows(#"Table A", each [Item] = varItemNeeded)[Pcs to complete request]{0}
                in
                List.Sum(
                    Table.SelectRows(#"Added Index", each [Index] >= varIndex)[Pallet Pcs]
                ) <= varPiecesNeeded,
                type logical
        ),
    #"Added Pallet Needed" = 
        Table.AddColumn(#"Added Enough Pieces", "Pallet Needed", 
        each 
            let
                varLastIndex = 
                    List.Min(
                        Table.SelectRows(#"Added Enough Pieces", each [Enough Pieces] = true)[Index]
                    )
            in
            [Index] <= varLastIndex,
            type logical
        ),
    #"Filtered Rows To Keep What Is Needed" = Table.SelectRows(#"Added Pallet Needed", each ([Pallet Needed] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows To Keep What Is Needed",{"Item", "Pallet ID", "Pallet Pcs"})
in
    #"Removed Other Columns"

[Anonymous]

Yes, he's brilliant, and I agree: M code is beautiful. 
But now I'm feeling me like a fool. I use the code that u gave to me @edhans, but now I don't know what I'm doing wrong...
I only changed the source for tables A, B, and C, and when I refresh, the M code brings me wrong pallets.

It brings me this;

Where it should bring me the next pallets;

Here's my PBIX file what can I do to fix it?
Just like I said: I only change the sources for my local tables... 
This PBIX is driving me insane. 

[edhans]

@Anonymous  - I couldn't look at the M code in your PBIX file because it was tied to Excel files on your hard drive.

 But this is what I did in the file I linked to.

1. table A is the pallets I need.
   1. 
2. Table C starts like this:
   1. 
3. Then it is transformed to this table based on the need of 100 pallets for that item, in descending order of the pallet pieces, i.e. get the biggest qty pallets first
   1. 
4. And that is what is loaded to the model. 

If you want to represent that in DAX you could use the CONCATENATEX() function.

Pallet Summary = 
    CONCATENATEX(
        'Table C',
        "Pallet "
            & FORMAT(
                'Table C'[Pallet ID],
                "#"
            ) & ", "
            & FORMAT(
                'Table C'[Pallet Pcs],
                "#"
            ) & "pcs",
        ", "
            & UNICHAR( 10 )
    )

Ignore table B. That was one where we did it earlier 100% DAX and required a much more complex DAX measure. Plus, I am not sure how I would do the measure now since you cannot add an index to a DAX table in memory - that I am aware of.

So I think the combination of Power Query to shape the data to bring what you need and DAX to analyze and summarize is the better method vs trying to do 100% of the exercise on either side.

You can get the file from the same link as above. I saved the changes with this measure in it. The first table on that Power Query tab is basically just a matrix showing you what is in the data with no DAX summarizing it.