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

[kubouch]

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.

[jbwtp]

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]

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 · Accepted solution]

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