Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
edhans
Community Champion
6 years ago

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.