How would this DAX code look in M?

[rpinxt]

So I have this DAX code to add a column:

MaxLog = MAXX( FILTER('1UAL_Data','1UAL_Data'[Delivery] = EARLIER( '1UAL_Data'[Delivery] )),'1UAL_Data'[LoggedOnDate_Local])

Now I want to add this column already in a dataflow as a step in power query.

But then the code needs to be in M.

Does anybody know how this would translate to power query code?

[JW_van_Holst]

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzTSNzIwMlLSUTKy1Dc0hHBiddDlLKByxphyhiZQOROwnKEpkpyBMZyDIQdk4ZRDsiA2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Delivery = _t, LoggedOnDate_Local = _t]),
Base = Table.TransformColumnTypes(Source,{{"Delivery", type date}, {"LoggedOnDate_Local", type date}}),
#"Added Custom" =
Table.AddColumn(
Base,
"Max_LoggedOnDate",
(outer)=> List.Max(Table.SelectRows(Base, each outer[Delivery] = [Delivery])[LoggedOnDate_Local])
)
in
#"Added Custom"

[rpinxt]

Thanks @JW_van_Holst 

I indeed do not understand all that code that you are using but I do get a table as extra column:

However when I try to get only the field in we need it gives an error:

Something about the date.

And 15/12/2022 would not be a correct Max log date for a line.

That would be todat and thats not possible.

Log date for that line is 8/5/2022 so seems it is not taking the max log for delivery line?

@goncalogeraldes I am doing this in a Dataflow with a Pro license?

Is this so much different than you suggested?

[goncalogeraldes]

Hi @JW_van_Holst ! Thanks for your contribution on this subject. I would like to add a note here. 

Whether or not one should use iterators in Power Query depends on the specific task that you're trying to accomplish. Iterators can be useful when you want to perform the same operation on a large number of elements in a list or table. However, it's important to note that iterators can be slow and inefficient, especially when used on large datasets. In Power Query, it's generally better to avoid iterators and use built-in functions and transformations instead, as these can often accomplish the same task in a more efficient and performant way. In summary, while iterators can be useful in some situations, it's generally better to avoid using them in Power Query whenever possible, and instead use built-in functions and transformations to achieve the desired result. Nevertheless, yours is a great approach and kudoes to you for that! 🙂

[rpinxt]

Hi @goncalogeraldes , thanks but why should I make a duplicate of the 1UAL_Data table?

I want add the M code to the original table and not use a calculated dax column in the pbix file I made based on the dataflow in which the query resides.

[goncalogeraldes]

You can just reference the original query instead of duplicating it. The reason why you need this is because otherwise, if you do the group by in the original table you will lose granularity. Also, the reason for the approach I suggested is because iterating large tables can largely increase your refresh times when the report is published. My approach can be less resource intensive and resource consuming.

For additional help, please @ me in your reply!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[rpinxt]

Ok thanks for this @goncalogeraldes .

But maybe doing it like this is not such a good idea then?

Maybe adding this table in your pbix report thru dax would be the smarter thing to do anyway?

Ps could you elaborate on this piece of code :

= Table.Group(Previous_Step_Name,

Guess previous step name would be how I named the copy of the original table?

And the table.group....what does it do? Do'nt understand why it should group things.

[goncalogeraldes]

It is always best if you all the transformations within Power Query since the tables go through the compression engine (and therefore take less space) and the DAX tables do not. 

One thing I did ask is of your license is Premium, otherwise this wont work on a dataflow and you will have to do it in a dataset.

[rpinxt]

Ah ok thanks for this info.

Unfortunately I have Pro license.

So then I will have to do it in DAX.

Or do you mean with Pro you can do it in a Dataset but not in a Dataflow?

[goncalogeraldes]

You can do it in a dataset with Pro license but not with a dataflow 🙂

[goncalogeraldes · Accepted solution]

Hi @rpinxt ! Create a duplicate of the '1UAL_Data' table and add the following step in the new query. Your M code may look something like:

= Table.Group(Previous_Step_Name, {"Delivery"}, {{"LoggedOnDate_Local", each List.Max([LoggedOnDate_Local]), type number}})

This code can be achieved with a "Group by" on Power Query. You can later merge this new query in the original table and expand the newly computed column!

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes