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
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
Custom1
Just add your tables to the list (tablesToAdd) and sbstitie main with the "foo" table.
Cheers,
John