Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Transfer calculated column into power query

Hello,
is it possible to bring this formula for a Calculated column directly into Power Query.

thanks in advance

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi Anonymous ,

     

    I think it would be something like this in a new custom column:

    if List.Contains(List.Buffer(previousStepName[Komponente]), [TopMaterial])
then null else "TopTopMat"

     

    Pete

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Anonymous ,

     

    I think it would be something like this in a new custom column:

    if List.Contains(List.Buffer(previousStepName[Komponente]), [TopMaterial])
then null else "TopTopMat"

     

    Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Pete,

    thanks for your support. Unfortunately this approach does not work. I get an error message. Probably because it is expected to return a single value, but the formula refers to the entire column. Do u have any other ideas?

    • BA_Pete's avatar
      BA_Pete
      Super User
      3 years ago

       

      What error do you get? This should work ok I think.

       

      -----------------------------------------------------------------------------------------

      *EDIT* Here's a query with this concept working:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpMVIrViQZRSGwkThKQmZQEYaKwkTjJQGZyMoSJwoZxYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, list = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"name", type text}, {"list", type text}}),
    addCustom = Table.AddColumn(chgTypes, "Custom", each
        if List.Contains(List.Buffer(chgTypes[list]), [name])
        then "MATCH"
        else null
    )
in
    addCustom

       

       

      Example query output:

       

      Pete