Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
If on Power Query , you try to left merge 2 big Query Tables , via 2 nullable columns an error will occur
It's due to a "bad" SQL Query folding generate by power query
Power Query add provide a strange code at the end , and if you remove it on Console , it will be then "OK"
Apparently Power BI & Google GCP don't want to fix this issue
For Power BI , it's a SQL limitation regarding left join from Big Query ;>) ;>) great humor
@Bamak , what are exact steps you are doing
Hi Amitchandac
I'm trying to merge 2 GCP 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.
If we remove the SQL red code on SQL Query , the issue disappears
A 2 years old issue , none power bi & GCP Bigquery want to fix it
Thanks a lot for any help and best regards
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |