Creating Table with UNION and DISTINCT is not merging data

[Anonymous]

Hi Community, 

I am trying to create a new table through DAX [not interested in Query Editor Solutions] from two source tables with the exact same columns in both tables and all columns having the TEXt data format. This is my query:

Articles Merged = 
UNION (
    DISTINCT(SELECTCOLUMNS ( 
        'GA - NU Actueel - Article Batch #1',
        "Date", 'GA - NU Actueel - Article Batch #1'[Date],
        "Client ID", 'GA - NU Actueel - Article Batch #1'[Client ID],
        "Session ID", 'GA - NU Actueel - Article Batch #1'[Session ID],
        "Hit Timestamp", 'GA - NU Actueel - Article Batch #1'[Hit Timestamp],
        "Vak", 'GA - NU Actueel - Article Batch #1'[Vak],
        "Artikel Niveau", 'GA - NU Actueel - Article Batch #1'[Artikel Niveau],
        "Artikel Datum", 'GA - NU Actueel - Article Batch #1'[Artikel Datum],
        "Artikel Titel", 'GA - NU Actueel - Article Batch #1'[Artikel Titel]
    )),
    DISTINCT(SELECTCOLUMNS ( 
        'GA - NU Actueel - Article Batch #2', 
        "Date", 'GA - NU Actueel - Article Batch #2'[Date],
        "Client ID", 'GA - NU Actueel - Article Batch #2'[Client ID],
        "Session ID", 'GA - NU Actueel - Article Batch #2'[Session ID],
        "Hit Timestamp", 'GA - NU Actueel - Article Batch #2'[Hit Timestamp],
        "Vak", 'GA - NU Actueel - Article Batch #2'[Vak],
        "Artikel Niveau", 'GA - NU Actueel - Article Batch #2'[Artikel Niveau],
        "Artikel Datum", 'GA - NU Actueel - Article Batch #2'[Artikel Datum],
        "Artikel Titel", 'GA - NU Actueel - Article Batch #2'[Artikel Titel]
    )))

But it does not merge the rows: 

Date	Client ID	Session ID	Hit Timestamp	Course	Level	News Date	Title
7/24/2019	1015449327.1548485247	1563958031113.etpzkyyip	2019-07-24T11:46:04.792+03:00			7/24/2019	Ramp MH17 houdt Nederland al 5 jaar in zijn greep
7/24/2019	1015449327.1548485247	1563958031113.etpzkyyip	2019-07-24T11:46:04.792+03:00	MBO Burgerschap	(not set)

As you can see the first four columns have the same data and I would have expected that the last remaining four columns would be merged due to the blank values in the fifth and sixth column in the first data row and the blank values in the seventh and eigth column in the second data row. But it is not merging the data.

Is anyone able to help me out?

[DataInsights]

@Anonymous,

Try this solution.

1. Create calculated table that contains the distinct primary key columns of each table.

UnionTable = 
VAR vTable1 =
    SELECTCOLUMNS (
        Table1,
        "Date", Table1[Date],
        "Client ID", Table1[Client ID],
        "Session ID", Table1[Session ID],
        "Hit Timestamp", Table1[Hit Timestamp]
    )
VAR vTable2 =
    SELECTCOLUMNS (
        Table2,
        "Date", Table2[Date],
        "Client ID", Table2[Client ID],
        "Session ID", Table2[Session ID],
        "Hit Timestamp", Table2[Hit Timestamp]
    )
VAR vResult =
    DISTINCT ( UNION ( vTable1, vTable2 ) )
RETURN
    vResult

2. In the calculated table above (UnionTable), create calculated columns for each non-key column. These are the columns that need to be merged. The assumption is that only one table will have a value for each non-key column.

Course = 
VAR vTable1 =
    LOOKUPVALUE (
        Table1[Course],
        Table1[Date], UnionTable[Date],
        Table1[Client ID], UnionTable[Client ID],
        Table1[Session ID], UnionTable[Session ID],
        Table1[Hit Timestamp], UnionTable[Hit Timestamp]
    )
VAR vTable2 =
    LOOKUPVALUE (
        Table2[Course],
        Table2[Date], UnionTable[Date],
        Table2[Client ID], UnionTable[Client ID],
        Table2[Session ID], UnionTable[Session ID],
        Table2[Hit Timestamp], UnionTable[Hit Timestamp]
    )
VAR vResult =
    IF ( ISBLANK ( vTable1 ), vTable2, vTable1 )
RETURN
    vResult

Level = 
VAR vTable1 =
    LOOKUPVALUE (
        Table1[Level],
        Table1[Date], UnionTable[Date],
        Table1[Client ID], UnionTable[Client ID],
        Table1[Session ID], UnionTable[Session ID],
        Table1[Hit Timestamp], UnionTable[Hit Timestamp]
    )
VAR vTable2 =
    LOOKUPVALUE (
        Table2[Level],
        Table2[Date], UnionTable[Date],
        Table2[Client ID], UnionTable[Client ID],
        Table2[Session ID], UnionTable[Session ID],
        Table2[Hit Timestamp], UnionTable[Hit Timestamp]
    )
VAR vResult =
    IF ( ISBLANK ( vTable1 ), vTable2, vTable1 )
RETURN
    vResult

News Date = 
VAR vTable1 =
    LOOKUPVALUE (
        Table1[News Date],
        Table1[Date], UnionTable[Date],
        Table1[Client ID], UnionTable[Client ID],
        Table1[Session ID], UnionTable[Session ID],
        Table1[Hit Timestamp], UnionTable[Hit Timestamp]
    )
VAR vTable2 =
    LOOKUPVALUE (
        Table2[News Date],
        Table2[Date], UnionTable[Date],
        Table2[Client ID], UnionTable[Client ID],
        Table2[Session ID], UnionTable[Session ID],
        Table2[Hit Timestamp], UnionTable[Hit Timestamp]
    )
VAR vResult =
    IF ( ISBLANK ( vTable1 ), vTable2, vTable1 )
RETURN
    vResult

Title = 
VAR vTable1 =
    LOOKUPVALUE (
        Table1[Title],
        Table1[Date], UnionTable[Date],
        Table1[Client ID], UnionTable[Client ID],
        Table1[Session ID], UnionTable[Session ID],
        Table1[Hit Timestamp], UnionTable[Hit Timestamp]
    )
VAR vTable2 =
    LOOKUPVALUE (
        Table2[Title],
        Table2[Date], UnionTable[Date],
        Table2[Client ID], UnionTable[Client ID],
        Table2[Session ID], UnionTable[Session ID],
        Table2[Hit Timestamp], UnionTable[Hit Timestamp]
    )
VAR vResult =
    IF ( ISBLANK ( vTable1 ), vTable2, vTable1 )
RETURN
    vResult

[Anonymous]

Hi @DataInsights, 

Thank you so much for showing this solution. I see the distinct doing its job, but I get errors for two of the columns and the last column returns a blank value. The error of the two columns with errors is: "A table of multiple values was supplied where a single value was expected."

Does this mean that the source tables: Table1 and Table2 should also not contain any duplicates for Date, Client ID, Session ID and Hit Timestamp for this solution to work?

[DataInsights]

@Anonymous,

1. It sounds as if the error is caused by the following:

"If multiple rows match the search values and in all cases Result_Column values are identical then that value is returned. However, if Result_Column returns different values an error is returned."

https://dax.guide/lookupvalue/ 

If this is the case, removing the duplicate rows should resolve the issue.

2. What value do you expect to be in the last column where a blank is being returned? Could you provide the data in Table1 and Table2 for this row (all columns)?

[Anonymous]

@DataInsights,

Thank you for the reply. I removed duplicates for both tables for the four columns that I was combining and this did indeed solve the errors. However, the last column still returns a blank value. Looking at my previous example I only have one row in both source tables. These are the rows which I have attached in my original message. The last column value I am expecting back is 'Ramp MH17 houdt Nederland al 5 jaar in zijn greep', but it return a blank. 

[DataInsights · Accepted solution]

@Anonymous,

It's likely due to the Title column containing a zero-length string instead of a blank. Click the drop-down for this column in Data view:

I named your original column TitleRaw, and created (in Power Query) a new column Title to transform the zero-length string into a blank:

if [TitleRaw] = "" then null else [TitleRaw]

Now the Title column has a blank value:

[Anonymous]

@DataInsights You needed to guide me step by step, but we have an expected end result. It Works! Thank you so much for your help. I accepted this as a solution.

[DataInsights]

@Anonymous,

Great! Glad to hear it works.