Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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;

temRequestOnSite PcsOffSite PcsPcs to complete request
609048-109-1012020160100


Table B;

ItemPallet IDPallet Pcs
609048-109-10710
609048-109-101040
609048-109-101220
609048-109-101320
609048-109-10920
609048-109-101140
609048-109-10810

 

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.

ItemPallet IDPallet Pcs
609048-109-101040
609048-109-101140
609048-109-10920
609048-109-101220
609048-109-101320
609048-109-10710
609048-109-10810

 

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.




3 REPLIES 3
edhans
Super User
Super User

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.

edhans_0-1594394767284.png

 

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"

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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;

javiermontess_0-1594421519775.png

Where it should bring me the next pallets;

javiermontess_1-1594421640504.png

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.

  1. table A is the pallets I need.
    1. edhans_0-1594502259018.png

       

  2. Table C starts like this:
    1. edhans_1-1594502306585.png

       

  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. edhans_2-1594502401572.png

       

  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 )
    )

 

edhans_3-1594503081746.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.