Forum Discussion
How would this DAX code look in M?
So I have this DAX code to add a column:
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
10 Replies
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 GeraldesHi 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.
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 GeraldesOk 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.
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"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! 🙂
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?
