cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bamak
Helper V
Helper V

Query M - Merge 2 Big Query tables - Lef join issue

Hi,

I'm trying to merge 2 bigquery tables in query M  via Left outer , but i'm facing a strange issue 

Info :  If inner join , there is no problem

 

Exception:
ExceptionType: Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Message: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.
StackTrace:
à Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcUtils.HandleErrorCheckNoData(OdbcHandle hrHandle, RetCode retcode)
à Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteStatement(String commandText, IList`1 parameters, OdbcStatementHandle statement, OdbcBuffer& parameterBuffer)
à Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)
à Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)
à Microsoft.Mashup.Engine1...

For info , query M code --> 

let
Source = GoogleBigQuery.Database([]),
.....
#"Requêtes fusionnées" = Table.NestedJoin(#"Autres colonnes supprimées", {"string_field_0"}, accesslog, {"string_field_0"}, "accesslog", JoinKind.LeftOuter),
#"accesslog développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "accesslog", {"string_field_0"}, {"accesslog.string_field_0"})
in
#"accesslog développé"

 

Folding request could be finding in Query M diagnosis traker -->

 

select `OTBL`.`string_field_0`,
`ITBL`.`string_field_0` as `C1`
from
(
select `string_field_0`
from `CCCCC`.`CCCCC`accesslog2`
where (`string_field_13` = 'http://www.google.com' and `string_field_13` is not null) and ((`string_field_0` = '10.0.235.71' and `string_field_0` is not null or `string_field_0` = '10.1.160.165' and `string_field_0` is not null) or (`string_field_0` = '10.1.238.116' and `string_field_0` is not null or `string_field_0` = '10.1.54.193' and `string_field_0` is not null))
) as `OTBL`
left outer join
(
select `string_field_0`
from `CCCCC`.`CCCCC`.`accesslog`
where (`string_field_13` = 'http://www.google.com' and `string_field_13` is not null) and ((`string_field_0` = '10.0.235.71' and `string_field_0` is not null or `string_field_0` = '10.1.160.165' and `string_field_0` is not null) or (`string_field_0` = '10.1.238.116' and `string_field_0` is not null or `string_field_0` = '10.1.54.193' and `string_field_0` is not null))
) as `ITBL` on ((`OTBL`.`string_field_0` = `ITBL`.`string_field_0` and `OTBL`.`string_field_0` is not null) and `ITBL`.`string_field_0` is not null or `OTBL`.`string_field_0` is null and `ITBL`.`string_field_0` is null)
LIMIT 1000 OFFSET 0 

 

Who effectively show an issue when running on GCP BigQuery Console  -->

LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

 
 

Thanks a lot for any help and best regards

 

Christophe 

 

 

 

 

1 ACCEPTED SOLUTION

Hi

apparently the columns used in left join must be in REQUIRED mode
The query folding will be more refined, without OR conditions in the ON

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Bamak , Have you give a custom query in advance option. Or generated by power bi.

If it generated by power bi and giving error, log an issue at -https://community.powerbi.com/t5/Issues/idb-p/Issues

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi Amit

 

Query is generated 100% by Desktop (query M)

4 steps only

Issue on a "simple" merge  left join

 

Thanks a lots for informations

 

 

Hi

apparently the columns used in left join must be in REQUIRED mode
The query folding will be more refined, without OR conditions in the ON

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors