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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
v-xinruzhu-msft
Community Support
Community Support

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
v-xinruzhu-msft
Community Support
Community Support

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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