Power BI / query / BigQuery / Left merg issue

[Bamak]

Hi,

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

1)

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...

2)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é"

3) 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 

4) 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.

5) Further information:

•    The orange colored part of the BQ request seems to be the problem
•     If using an inner join instead of a left join, there is no observed problem
•     If the 2 columns used for the merge relation are in mode: REQUIRED also no problem occurs

Thanks a lot for any help and best regards

[v-lili6-msft]

hi @Bamak 

I search for this case, it seems that it is a common problem in BigQuery, even if you don't use this query in power query.

You could also create a support ticket for further help.

Regards,

Lin