Hi all,
Does someone have a function that can automatically compare two tables/queries with the same column names and predefined key? The result should be simple 0 or 1 per cell.
So for instance. Query A & B, both have column X (= key), Y & Z. In the function you select A & B, select X as key and then you get an overview per key of Y & Z.
Regards,
Christopher
Hi @Wlknsn,
I’d be happy to look into this with you but can you illustrate your requirement with an actual example in excel, so we have something to work with?
Thanks!
Hi @Wlknsn, we’ve noticed that no response has been received from you since Sep 3.
In case there won’t be any activity on this post, in the next few days, we’ll be tagging this post as Solved.
Hi @Wlknsn due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread or provide the necessary information requested by @Melissa above.
@Melissa Attached a powerbi file with two simple tables. Both have the exact same model. The point is that you would select in the function which column the key is, which tables to compare and that automatically the function will compare all other columns and for each value say whether it’s the same (1) or not (0).
Result:
Hi @Wlknsn,
I might have gone a little overboard… but give this a go:
let fxCom2Tables = ( lTable as table, lKeyColNum as number, rTable as table, rKeyColNum as number, optional Comparison as nullable number ) as table =>
let
BaseTable = Table.Buffer( lTable ),
lKeyColName = Table.ColumnNames( BaseTable ){lKeyColNum},
CompareAs = if Comparison =1 then Comparer.OrdinalIgnoreCase else Comparer.Equals,
AddKeyL = Table.AddColumn( BaseTable, "KeyColCase", each Text.Upper(Text.From( Record.FieldValues(_){lKeyColNum} )), type text ),
AddKeyR = Table.AddColumn( rTable, "KeyColCase", each Text.Upper(Text.From( Record.FieldValues(_){rKeyColNum} )), type text ),
Merge =
if Comparison = 1
then Table.NestedJoin( AddKeyL, {"KeyColCase"}, AddKeyR, {"KeyColCase"}, "Temp", JoinKind.LeftOuter)
else Table.NestedJoin( BaseTable, lKeyColName, rTable, Table.ColumnNames( rTable ){rKeyColNum}, "Temp", JoinKind.LeftOuter),
CompareRecord = Table.AddColumn( Merge, "Compare", each let ToRemoveCols = {lKeyColName} & {"Temp", "KeyColCase"} in
if Table.IsEmpty( _[Temp] ) then List.Repeat( {0}, Record.FieldCount( Record.RemoveFields(_, ToRemoveCols, MissingField.Ignore ))) else
List.Transform(
List.Zip(
{
Record.FieldValues( Record.ReorderFields( Record.RemoveFields(_, ToRemoveCols, MissingField.Ignore ), Table.ColumnNames(Merge), MissingField.Ignore )),
Record.FieldValues( Record.ReorderFields( Record.RemoveFields(_[Temp]{0}, ToRemoveCols, MissingField.Ignore ), Table.ColumnNames(Merge), MissingField.Ignore ))
}
), each try if _{0} = _{1} then 1 else 0 otherwise 0
)),
tResult =
Table.SplitColumn(
Table.TransformColumns(
Table.SelectColumns( CompareRecord, {lKeyColName} & {"Compare"} ),
{"Compare", each Text.Combine(List.Transform(_, Text.From), "||"), type text}),
"Compare", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), List.Select( List.Difference( Table.ColumnNames(Merge), {"Temp", "KeyColCase"} ), each _ <> lKeyColName ))
in
tResult, Documentation = [
Documentation.Name = "fxCompare2Tables",
Documentation.LongDescription = "Returns 0 for each field value that doesn't match and a 1 for each field value that does match.",
Documentation.Category = "Table",
Documentation.Author = "Melissa de Korte",
Documentation.Examples =
{
[
Description = " ",
Code = "How variables should be used. #(lf)
lKeyColNum and rKeyColNum as zero based position of the Key Column #(lf)
optional Comparison as number if set to 1 the case of Key column values will be ignored",
Result = " "
]
}
]
in
Value.ReplaceType( fxCom2Tables, Value.ReplaceMetadata( Value.Type( fxCom2Tables ), Documentation ))
.
You can optionally disable case sensiivity for key column comparison by entering a 1.
Returns this result when applied to your sample.
let fxCompare2Tables = ( lTable as table, lKeyColNum as number, rTable as table, rKeyColNum as number ) as table =>
let
lKeyColName = Table.ColumnNames( lTable ){lKeyColNum},
rKeyColName = Table.ColumnNames( rTable ){rKeyColNum},
tResult =
Table.FromColumns(
{ Table.ToColumns( lTable ){lKeyColNum} } &
List.Transform(
List.Zip(
{
Table.ToColumns( Table.RemoveColumns( lTable, lKeyColName, MissingField.Ignore )),
Table.ToColumns( Table.RemoveColumns( rTable, rKeyColName, MissingField.Ignore ))
}
),
each List.Transform( List.Zip( { _{0}, _{1} } ), (i) => try if i{0} = i{1} then 1 else 0 otherwise 0 )
), { lKeyColName } & Table.ColumnNames( Table.RemoveColumns( lTable, lKeyColName, MissingField.Ignore ))
)
in
tResult, Documentation = [
Documentation.Name = "fxCompare2Tables",
Documentation.LongDescription = "Returns 0 for each field value that doesn't match and a 1 for each field value that does match.",
Documentation.Category = "Table",
Documentation.Author = "Melissa de Korte",
Documentation.Examples =
{
[
Description = " ",
Code = "How variables should be used. #(lf)
lKeyColNum and rKeyColNum as zero based position of the Key Column",
Result = " "
]
}
]
in
Value.ReplaceType( fxCompare2Tables, Value.ReplaceMetadata( Value.Type( fxCompare2Tables ), Documentation ))
I hope this is helpful
Hi @Wlknsn did the response provided by Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi Melissa, testing this today. So looking forward to it!!!
Many thanks for all the work once again.
Hello @Wlknsn, we’ve noticed that no response has been received from you since Sep 10.
We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Wlknsn, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread.