Join DirectQuery and Imported tables in DAX

[Anonymous]

I have a DirectQuery table (Weather) which is sourced from an Azure SQL server. I would like to join this with an Imported table (Buckles) from an Excel sheet sourced from SharePoint Online.

Both tables have a UID field that is made up of a concatenation between a SiteID and timestamp. The UID field is named differently for each table.

I have created a One-To-Many relationship between the two tables.

I have tried to create a new DAX table using a NATURALINNERJOIN on Weather and Buckles but I get this error:

"No common join columns detected. The join function 'NATURALINNERJOIN' requires at-least one common join column."

I am confident it is not a problem with the underlying data because I've created a new imported Excel table (Test) with a selection of the data from Weather and I'm able to successfully create the join on Test and Buckles.

Is the joining of DirectQuery and Imported tables supported? I feel like this may be a type casting issue, but as far as I can see, both UID fields are set as Text.

[amitchandak]

@Anonymous , just check you join is Active. Also, check there no space in the names.

Refer this : https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[Anonymous]

When you say the join is active, do you mean the relationship? I think that's already the case.

Definitely no space in the names.

I've reviewed the link but it doesn't really give any more details. I'm following the guidance by creating a relationship between the relevant fields and then using the NATURALINNERJOIN on a new DAX table

[Anonymous]

Hi @Anonymous ,

Please mark sure the relationship has been created between Weather and Buckles table based on field UID first, then used the method as the link provided by @amitchandak to create the new table as below:

NewTable=
VAR buckles_TreatAs =
    TREATAS ( Weather, Buckles[UID], Weather[field1], Weather[field2] )
VAR Result =
    NATURALLEFTOUTERJOIN ( buckles_TreatAs, Buckles)
RETURN
    Result

Best Regards

Rena

[Anonymous]

Hi,

So I've tried your solution:

Merge2 = 
 VAR buckles_TreatAs =
    TREATAS ( Weather, 'Buckle Data'[UID_Date], Weather[DateTime], Weather[DateTime - Copy], Weather[Radiation], Weather[RelativeHumidity], Weather[SMILevel1], Weather[SMILevel2], Weather[SMILevel3], Weather[SMILevel4], Weather[Snowfall], Weather[Temperature], Weather[TotalPrecipitation], Weather[WeatherCell], Weather[WeatherCell - Copy], Weather[WindDirection], Weather[WindGust], Weather[WindSpeed] )
VAR Result =
    NATURALINNERJOIN ( buckles_TreatAs, 'Buckle Data')
RETURN Result

My Buckles table is actually called 'Buckles Data' in my model.

As far as I understand, I need to supply the TREATAS function all of the columns in the Weather table, these are a mix of data types.

I now get the following error when trying to run the function:

Cannot convert value '2367:Apr 2 2006 3:00PM' of type Text to type Integer while processing column - Weather[UID_Weather] -> Weather[WindSpeed].

The value shown here is the first UID value from the Buckles Data table. I'm not sure why it's trying to convert it to an integer, nor why it's processing data between the UID_Weather and WindSpeed columns.

I have the relationship set up as One-to-Many between Weather[UID_Weather] and 'Buckles Data'[UID_Date]

[Anonymous]

Hi @Anonymous ,

Could you please provide some sample data both in Weather and Buckle Data table in form of screen shot or table(include fields and data) in order to make troubleshooting on data convert issue? Thank you.

Best Regards

Rena

[Anonymous]

Buckle Data - This is imported from an Excel sheet. The UID_Date column is type Text

Weather - This is a DirectQuery result from an Azure SQL DB

Note: Here I'd tried renaming the column to matched on (UID_Date) to be the same as the column in Buckle Data, but with no effect. I have already tried this with the column named differently (UID_Weather).

[Anonymous]

Hi @Anonymous ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help others find the solution easily if they face the same problem with you. Thank you.

Best Regards

Rena

[Anonymous]

Hi @Anonymous ,

Please try to merge the table Weather and Buckle Data in Power Query Editor:

Merge Queries as NewResult

Best Regards

Rena