Creating Table with UNION and DISTINCT is not merging data

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?

Need Help

DataInsights
5 years ago
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:

7 Replies

DataInsights

Super User

5 years ago

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
Not applicable
5 years ago
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
  Super User
  5 years ago
  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)?

Forum Discussion

Creating Table with UNION and DISTINCT is not merging data

7 Replies

Related content

Merge statement creates duplicates - Low shuffle merge as cause?

Table Merge creates duplicate records

MERGE QUERIES

count distinct

Async merge risk