SQL compilation error: ambiguous column name

[yoon]

Power BI services, connected to Snowflake, generates SQL that triggers an error: " Unable to connect to the data source ODBC: ERROR [42601] SQL compilation error: ambiguous column name 'name'."

The tables and attributes in question are DEALS.name, SCOPES.name, SUB_SCOPES.name, and QUESTIONS.name. They are related to each other from left to right in one to many relationships using "Manage Relationships".

select "C16",
    { fn convert(count(distinct("name")), SQL_DOUBLE) } + { fn convert(max("C1"), SQL_DOUBLE) } as "C1"
from 
(
    select "OTBL"."name",
        "ITBL"."name" as "C16",
        case
            when "name" is null
            then CAST(1 as INTEGER)
            else CAST(0 as INTEGER)
        end as "C1"
    from 
    (
        select "OTBL"."name",
            "OTBL"."title",
            "OTBL"."type",
            "OTBL"."input_type",
            "OTBL"."id",
            "OTBL"."created_at",
            "OTBL"."updated_at",
            "OTBL"."sub_scope_id",
            "OTBL"."discarded_at",
            "OTBL"."publish_data_at",
            "OTBL"."deal_id",
            "OTBL"."C1",
            "OTBL"."C2",
            "OTBL"."C3",
            "OTBL"."scope_id",
            "OTBL"."C4",
            "OTBL"."C5",
            "OTBL"."C6",
            "OTBL"."C7",
            "ITBL"."name" as "C8",
            "ITBL"."title" as "C9",
            "ITBL"."id" as "C10",
            "ITBL"."created_at" as "C11",
            "ITBL"."updated_at" as "C12",
            "ITBL"."deal_id" as "C13",
            "ITBL"."discarded_at" as "C14",
            "ITBL"."publish_data_at" as "C15"
        from 
        (
            select "OTBL"."name",
                "OTBL"."title",
                "OTBL"."type",
                "OTBL"."input_type",
                "OTBL"."id",
                "OTBL"."created_at",
                "OTBL"."updated_at",
                "OTBL"."sub_scope_id",
                "OTBL"."discarded_at",
                "OTBL"."publish_data_at",
                "OTBL"."deal_id",
                "ITBL"."name" as "C1",
                "ITBL"."title" as "C2",
                "ITBL"."id" as "C3",
                "ITBL"."scope_id",
                "ITBL"."created_at" as "C4",
                "ITBL"."updated_at" as "C5",
                "ITBL"."discarded_at" as "C6",
                "ITBL"."publish_data_at" as "C7"
            from 
            (
                select "name",
                    "title",
                    "type",
                    "input_type",
                    "id",
                    "created_at",
                    "updated_at",
                    "sub_scope_id",
                    "discarded_at",
                    "publish_data_at",
                    "deal_id"
                from "STAGING"."PUBLIC"."QUESTIONS"
                where "discarded_at" is null and not "publish_data_at" is null
            ) as "OTBL"
            left outer join 
            (
                select "name",
                    "title",
                    "id",
                    "scope_id",
                    "created_at",
                    "updated_at",
                    "discarded_at",
                    "publish_data_at"
                from "STAGING"."PUBLIC"."SUB_SCOPES"
                where "discarded_at" is null and not "publish_data_at" is null
            ) as "ITBL" on ("OTBL"."sub_scope_id" = "ITBL"."id")
        ) as "OTBL"
        left outer join 
        (
            select "name",
                "title",
                "id",
                "created_at",
                "updated_at",
                "deal_id",
                "discarded_at",
                "publish_data_at"
            from "STAGING"."PUBLIC"."SCOPES"
            where "discarded_at" is null and not "publish_data_at" is null
        ) as "ITBL" on ("OTBL"."scope_id" = "ITBL"."id")
    ) as "OTBL"
    left outer join 
    (
        select "external_id",
            "industry",
            "sub_industry",
            "location",
            "name",
            "vendor_id",
            "client_contact",
            "investment_thesis",
            "id",
            "created_at",
            "updated_at",
            "updated_from_vendor_at",
            "scopes_protected_at",
            "discarded_at",
            "report_date",
            "response_import_id",
            "selected",
            "vendor2_id",
            "vendor_target_account_id",
            "publish_data_at"
        from "STAGING"."PUBLIC"."DEALS"
        where "discarded_at" is null and not "publish_data_at" is null
    ) as "ITBL" on ("OTBL"."C13" = "ITBL"."id")
) as "ITBL"
group by "C16"
LIMIT 1000001 OFFSET 0

The expected behavior would be for Power BI to generate SQL that would allow you to have columns with the same name in each table and still perform joins.

[v-yuta-msft]

@yoon ,

This issue seems to be more related to SQL, I would suggest you post this thread to SQL forum.

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msftThis does not belong on a SQL  forum because it involves how PBI auto-generates SQL. The error from a SQL perspective is expected, and a human SQL developer would be able to fix it easily. I was able to make a weird work-around fix the issue by adding a step at the end that changed the name of the column to anything.

[jacob-dickey]

I have this same problem. I tried renaming all of the columns uniquely within the report, but it did not work. I'm not sure how to fix this as I can't alter the code that Power BI generates for DirectQuery. 

[Anonymous]

@jacob-dickey I had the same "ambiguous column name" error message come up on a table that had worked fine before. I found a solution, but I don't know if it will apply to your situation.

My issue arose after adding a conditional column in Power Query. The main table had always had two source tables each with a field named "name". Before the tables were merged one of the "name" fields was renamed to "role". This worked fine until a conditional column was added to the main table with logic that checked the "role" field. After this conditional column was added the "ambiguous column name" error came up in Power BI.

The solution was to add the conditional column before the source tables were merged. I used the same conditional column rules on the source table that had caused the error on the main table. The merge was updated to add this new column to the main table and the error was resolved.

The order of the operations could provide a solution.

[Anonymous]

I'm having the same issue and having a hard time following the recommendations left by @Anonymous, unfortunately. I would really appreciate some additonal details or a screenshot to help illustrate how you made the update to resovle this issue.