Compare columns from different tables in one table visual

[MoOnan]

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

[Anonymous · Accepted 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.

[MoOnan]

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!