Custom column M language filter formula translation from desktop new column formula

[Vauban]

Hi to all.

I have a formula that @amitchandak graciously helped me on.  Unfortunately I realized I should have created it in the Query Editor instead of just making a new column in the tabs part of desktop. Calculated columns don't migrate to the Query Editor for where I want to do additional work with it.

This is the new column formula:

MinDatePerStatePerWorkItemId = minx(filter('PPM for BI','PPM for BI'[Work Item Id] = earlier('PPM for BI'[Work Item Id]) && 'PPM for BI'[State] = earlier('PPM for BI'[State])),'PPM for BI'[Date])

This is my effort trying to use Query Editor's Custom Column function:

MinDatePerStatePerWorkItemId  = minx(Table.SelectRows(#"PPM for BI",#"PPM for BI"[Work Item Id] = earlier(#"PPM for BI"[Work Item Id]) && #"PPM for BI"[State] = earlier(#"PPM for BI"[State])),#"PPM for BI"[Date])

I get the error message "Token Literal expected" at the red highlighted letter above.

Appreciate all help getting this resolved.

Also, if anyone has a good book on learning PBI's M language I would love to hear it.  Don't see a lot of selection out there but I do want to learn.

Best to all,

David

[Anonymous]

Hi,

M is a completely different language than DAX, so you can never reuse any formulas. You can find the M reference here:

https://docs.microsoft.com/en-us/powerquery-m/

Sometimes, like in this case when you want to find other rows from the same table to calcualte something, DAX could be a lot easier than M. If it works, why don't use it? Compression of the data might be less optimal when using calculated columns in DAX, but if you don't have huge amounts of data it probably doesn't matter.

Best Regards // Ulf

[Vauban]

Thank you @Anonymous. 

I played with the Custom Column in Query Editor some more.

Because I didn't see how writing DAX into my dashboard application would work given that I have to go to Query Editor and do more calcs on whatever columns I bring in there.

So this is what I came up with for the Custom Column formula in Query Editor:

MinDatePerStatePerWorkItemId = minx(filter([Work Item Id] = earlier([Work Item Id]) & [State] = earlier([State])),[Date])

I got excited that there weren't any syntax errors but the not finding minx error is a problem of course.

I looked for what other formula names besides "minx" in this situation is there but couldn't see it.

Thank you for any continued thoughts on this.

Best regards

[dax · Accepted solution]

Hi @Vauban , 

You are using DAX instead of power query. If you want to achieve this in power query, you could refer to below code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYVOlWB0E18gAzjUCCRmBuUkwxSg8UzgPpNQChWcG5xmDMTIPaGYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, id = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"id", Int64.Type}, {"amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"name", "id"}, {{"mivx", each List.Min([amount]), type number}, {"all", each _, type table [name=text, id=number, amount=number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"amount"}, {"amount"})
in
    #"Expanded all"

If you want to use your expression, you need to create calculated column instead of power query, you need to create like below

 In addition, you also could use measure to achieve this, which will show in table visual, you could try below expression

Measure 2 = CALCULATE(MIN(t[amount]), ALLEXCEPT(t,t[name],t[id]))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vauban]

@dax, thank you.  Your post made me realize I should make an index in the Query Editor.  From that I was able to use your grouping idea and save me from trying to code out of my depth.  Thank you @Anonymous for the link to learn more.  🙂