Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I have tables, which should contain similar data. I am trying to find missing or different records between those tables.
SQLTables
ObjectId | TableNameSQL |
1 | Companies |
2 | Address |
3 | Sales |
4 | Customers |
SQLColumns
ObjectId | ColumnNameSQL |
1 | Name_Id |
1 | Name |
2 | Country |
2 | City |
2 | Street |
2 | Company_Id |
3 | Company_Id |
3 | Product |
3 | Value |
3 | Name |
4 | Name |
LocalTables
FileId | TableNameLocal |
205 | Companies |
301 | Address |
104 | Sales |
LocalColumns
FileId | ColumnNameLocal |
205 | Name_Id |
205 | Name |
301 | Country |
301 | City |
301 | Street |
104 | CompanyId |
104 | Product |
104 | Value |
And this is the result I am trying to achieve:
TableNames | ColumnNameSQL | ColumnNameLocal |
Companies | Name_Id | Name_Id |
Companies | Name | Name |
Address | Country | Country |
Address | City | City |
Address | Street | Street |
Address | Company_Id | |
Sales | Company_Id | |
Sales | CompanyId | |
Sales | Name | |
Sales | Value | Value |
Sales | Product | Product |
I tried merging tables, creating common column with table names to create many to one relationships, lookupvalue and probably other things I already forgot and I've read all similar posts I could find, but still I am not able to get the above result. I am really stuck on this one, so I would be really grateful if someone could explain this to me step by step..
Solved! Go to Solution.
Hi @MoOnan
You can refer to the folloing example.
1.Create two rank table in two column tables.
Column = RANKX(FILTER(LocalColumns,[FileId]=EARLIER(LocalColumns[FileId])),[ ColumnNameLocal])
Rankx = RANKX(FILTER(SQLColumns,[ObjectId]=EARLIER(SQLColumns[ObjectId])),[ ColumnNameSQL])
2.Create a calculated table
Table =
VAR a =
GENERATESERIES ( 1, 5, 1 )
VAR b =
SUMMARIZE ( SQLTables, [TableNameSQL], [ObjectId] )
VAR c =
SUMMARIZE ( LocalTables, [ TableNameLocal], [FileId] )
VAR _uion =
UNION ( GENERATEALL ( a, b ), GENERATEALL ( a, c ) )
VAR _add1 =
ADDCOLUMNS (
_uion,
"SQLColumn",
MAXX (
FILTER (
SQLColumns,
[ObjectId] = EARLIER ( SQLTables[ObjectId] )
&& [Rankx] = EARLIER ( [Value] )
),
[ ColumnNameSQL]
),
"Localcolumn",
MAXX (
FILTER (
LocalColumns,
[FileId] = EARLIER ( SQLTables[ObjectId] )
&& [Column] = EARLIER ( [Value] )
),
[ ColumnNameLocal]
)
)
RETURN
ADDCOLUMNS (
_add1,
"Final",
SWITCH (
TRUE (),
COUNTROWS (
FILTER (
_add1,
[TableNameSQL] = EARLIER ( [TableNameSQL] )
&& [Localcolumn] = EARLIER ( [SQLColumn] )
)
) = 1,
MAXX (
FILTER (
_add1,
[TableNameSQL] = EARLIER ( [TableNameSQL] )
&& [Localcolumn] = EARLIER ( [SQLColumn] )
),
[Localcolumn]
),
[Localcolumn]
IN SUMMARIZE (
FILTER (
_add1,
[TableNameSQL] = EARLIER ( SQLTables[TableNameSQL] )
&& [SQLColumn] <> BLANK ()
),
[SQLColumn]
)
= FALSE (), [Localcolumn]
)
)
Put the sqlcolumn and final column to the table visual.and create a measure to the visual filter
Measure = IF(SELECTEDVALUE('Table'[Final])=BLANK()&&SELECTEDVALUE('Table'[SQLColumn])=BLANK(),0,1)
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MoOnan
You can refer to the folloing example.
1.Create two rank table in two column tables.
Column = RANKX(FILTER(LocalColumns,[FileId]=EARLIER(LocalColumns[FileId])),[ ColumnNameLocal])
Rankx = RANKX(FILTER(SQLColumns,[ObjectId]=EARLIER(SQLColumns[ObjectId])),[ ColumnNameSQL])
2.Create a calculated table
Table =
VAR a =
GENERATESERIES ( 1, 5, 1 )
VAR b =
SUMMARIZE ( SQLTables, [TableNameSQL], [ObjectId] )
VAR c =
SUMMARIZE ( LocalTables, [ TableNameLocal], [FileId] )
VAR _uion =
UNION ( GENERATEALL ( a, b ), GENERATEALL ( a, c ) )
VAR _add1 =
ADDCOLUMNS (
_uion,
"SQLColumn",
MAXX (
FILTER (
SQLColumns,
[ObjectId] = EARLIER ( SQLTables[ObjectId] )
&& [Rankx] = EARLIER ( [Value] )
),
[ ColumnNameSQL]
),
"Localcolumn",
MAXX (
FILTER (
LocalColumns,
[FileId] = EARLIER ( SQLTables[ObjectId] )
&& [Column] = EARLIER ( [Value] )
),
[ ColumnNameLocal]
)
)
RETURN
ADDCOLUMNS (
_add1,
"Final",
SWITCH (
TRUE (),
COUNTROWS (
FILTER (
_add1,
[TableNameSQL] = EARLIER ( [TableNameSQL] )
&& [Localcolumn] = EARLIER ( [SQLColumn] )
)
) = 1,
MAXX (
FILTER (
_add1,
[TableNameSQL] = EARLIER ( [TableNameSQL] )
&& [Localcolumn] = EARLIER ( [SQLColumn] )
),
[Localcolumn]
),
[Localcolumn]
IN SUMMARIZE (
FILTER (
_add1,
[TableNameSQL] = EARLIER ( SQLTables[TableNameSQL] )
&& [SQLColumn] <> BLANK ()
),
[SQLColumn]
)
= FALSE (), [Localcolumn]
)
)
Put the sqlcolumn and final column to the table visual.and create a measure to the visual filter
Measure = IF(SELECTEDVALUE('Table'[Final])=BLANK()&&SELECTEDVALUE('Table'[SQLColumn])=BLANK(),0,1)
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Oh wow, this is more complicated than I expected, it works perfect. You have no idea how long I was stuck on this problem, I am so grateful! Thank you for your time and your help!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
59 | |
56 | |
38 | |
29 |
User | Count |
---|---|
82 | |
62 | |
45 | |
41 | |
40 |