Forum Discussion

kubouch's avatar
kubouch
New Member
3 years ago
Solved

Custom column - Difference between columns in the current and another query

I have a simple problem: I need to create a custom column that is calculated as a difference between a column from another query and a current query.

 

In the Custom Column menu formula, I can do

 

 

[foo] - [score]

 

 

to create a difference between two columns in the current query no problem. However, whenever I use a column from another query, like this

 

 

another_query[score] - [score]

 

 

I get an error within each cell of the newly created column:

 

 

Expression.Error: We cannot apply operator - to types List and Number.
Details:
    Operator=-
    Left=[List]
    Right=0.307

 

 

 

It seems like it's applying the whole column from "another_query" to each cell instead of destructuring it like the columns in the current query. Is there any way I can solve this? I feel like I'm missing something obvious. I am very new to Power Query.

Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    This is not all that gloom :), you can use a function to template repeating actions.

    Something like this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1YlWMgaTJmDSFEyaKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [foo = _t]),
    main = Table.TransformColumnTypes(Source,{{"foo", type text}}),
    a1 = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [score = _t]),{{"score", Int64.Type}}),
    a2 = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [score = _t]),{{"score", Int64.Type}}),
    a3 = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [score = _t]),{{"score", Int64.Type}}),
    
    tablesToAdd = {a1, a2, a3},
    process = List.Accumulate(tablesToAdd, Table.ToColumns(main), (a, n)=> a & {n[score]}),
    columnNames = Table.ColumnNames(main) & List.Transform({1..List.Count(tablesToAdd)}, each Number.ToText(_, "score#")),
    Custom1 = Table.FromColumns(process, columnNames)

in
    Custom1

     

    Just add your tables to the list (tablesToAdd) and sbstitie main with the "foo" table.

     

    Cheers,

    John

3 Replies

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    Hi kubouch,

     

    Yes, 

    another_query[score]

    refrers to a column/field score in the another_query table and return it as a list type.

     

    There is no simple way to resolve it in the context that you provided. A very common solution for this would be to merge the tables first using some common fields to match rows. In your case it seems that adding index columns to both table and them match on those columns will help you to achieve the desired state. After which you should be able to refer columns as [foo] - [score] as they will be in the same column.

     

    Cheers,

    John

    • kubouch's avatar
      kubouch
      New Member
      3 years ago

      Thank you, that's disappointing, I didn't expect such a basic functionality to be missing.

       

      The problem with merging is:

      a) I need to do this for two dozen table pairs so any extra step adds a lot of manual labor

      b) The columns in both tables are named "score" so I would need to rename the merged column during merging. I guess that's possible?

       

      Anyway, I'll see what I can do. I might just collect it into a regular Excel table and to it there...

      • jbwtp's avatar
        jbwtp
        Memorable Member
        3 years ago

        This is not all that gloom :), you can use a function to template repeating actions.

        Something like this:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1YlWMgaTJmDSFEyaKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [foo = _t]),
    main = Table.TransformColumnTypes(Source,{{"foo", type text}}),
    a1 = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [score = _t]),{{"score", Int64.Type}}),
    a2 = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [score = _t]),{{"score", Int64.Type}}),
    a3 = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [score = _t]),{{"score", Int64.Type}}),
    
    tablesToAdd = {a1, a2, a3},
    process = List.Accumulate(tablesToAdd, Table.ToColumns(main), (a, n)=> a & {n[score]}),
    columnNames = Table.ColumnNames(main) & List.Transform({1..List.Count(tablesToAdd)}, each Number.ToText(_, "score#")),
    Custom1 = Table.FromColumns(process, columnNames)

in
    Custom1

         

        Just add your tables to the list (tablesToAdd) and sbstitie main with the "foo" table.

         

        Cheers,

        John