Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
MoOnan
Frequent Visitor

Compare columns from different tables in one table visual

Hi,

I have tables, which should contain similar data. I am trying to find missing or different records between those tables.

 

SQLTables

ObjectIdTableNameSQL
1Companies
2Address
3Sales
4Customers


SQLColumns

ObjectId ColumnNameSQL
1Name_Id
1Name
2Country
2City
2Street
2Company_Id
3Company_Id
3Product
3Value
3Name
4Name


LocalTables

FileId TableNameLocal
205Companies
301Address
104Sales


LocalColumns

FileId ColumnNameLocal
205Name_Id
205Name
301Country
301City
301Street
104CompanyId
104Product
104Value


And this is the result I am trying to achieve:

TableNamesColumnNameSQLColumnNameLocal
CompaniesName_IdName_Id
CompaniesNameName
AddressCountryCountry
AddressCityCity
AddressStreetStreet
AddressCompany_Id 
SalesCompany_Id 
Sales CompanyId
SalesName 
SalesValueValue
SalesProductProduct


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..

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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)

vxinruzhumsft_0-1682650737220.png

 

Output

vxinruzhumsft_1-1682650747332.png

 

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.

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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)

vxinruzhumsft_0-1682650737220.png

 

Output

vxinruzhumsft_1-1682650747332.png

 

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!

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.