Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes, 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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |