Forum Discussion
Double Sort or Sort into another previous Sort ? (into a M code)
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"
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.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.
- table A is the pallets I need.
-
- Table C starts like this:
-
- 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
-
- 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.
- table A is the pallets I need.