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

[Bamak]

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 

[amitchandak]

@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

[Bamak]

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

[Bamak · 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