Forum Discussion
Custom column - Difference between columns in the current and another query
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 Custom1Just add your tables to the list (tablesToAdd) and sbstitie main with the "foo" table.
Cheers,
John
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
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...
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 Custom1Just add your tables to the list (tablesToAdd) and sbstitie main with the "foo" table.
Cheers,
John