Forum Discussion

Vauban's avatar
Vauban
Icon for Helper I rankHelper I
6 years ago
Solved

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

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

Need Help
  • dax's avatar
    dax
    6 years ago

    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.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Vauban
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        dax
        Icon for Community Support rankCommunity Support
        6 years ago

        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.