Query Folding Name Collision

[Anonymous]

I have three tables I'm merging, each with a name column, from SQL Server.  When I try to merge on a uniquely named key, the fact that the name column exists in the data is causing a collission. On one of the merge tables I have renamed the Name column and then tried to merge it, but it doesn't seem to make a difference.

DataSource.Error: Microsoft SQL: The column 'Name' was specified multiple times for '$Outer'.
Details:
DataSourceKind=SQL
DataSourcePath=SQL Server
Message=The column 'Name' was specified multiple times for '$Outer'.
Number=8156
Class=16

There doesn't appear to be a way to send this as a frown in Power Query, did I just miss it?

The only work around I found was to add something that broke query folding, then the merge worked fine. 

[v-qiuyu-msft]

Hi @Anonymous,

From your description, there are three tables, each table only has one column with the same name [Name]. When you try to merge these three tables, the error occurs, right? 

Based on my test in the latest Power BI desktop version, though tables has the same column name, but after merge and expand, the new column will be name with table.columnname, eg: t1.Name. See: 

In your scenario, would you please update the Power BI desktop then test again? If issue persists, please share detail steps for us to reproduce the issue. It would be better if you could share some screenshots. 

Best Regards,
Qiuyun Yu 

[Anonymous]

@v-qiuyu-msft I updated from March 2018 to the latest available today, and it did not resolve the issue. 

I am connecting to SQL Server 2016, and after the connection one of the first steps is to rename the name column. On the 'main' table or what the others join to, the column is renamed from name to Volume.

On another table, I have a function that renames all of the columns from snake_case to Proper Case. This changes name to Name. I then rename after that step the specific Name column to Array.

The third table which is joined just receives the function to uppercase to Name, and then is joined to the main table. Then after is it joined, I join the table with the Array, and that's when I get the error as above for $Outer.

Below is the stripped down version of what I can post.

let
    Source = Sql.Database("db", "database"),
    Pool = Source{[Schema="dbo",Item="Pool"]}[Data],
    #"Alter Column Names" = Table.TransformColumnNames( Pool  , ( columnName as text ) as text => Text.Combine( List.Transform( Text.Split( columnName, "_" ), each if Text.Length( _ ) >= 4 then Text.Proper( _ ) else _ ), " ") )
in
    #"Alter Column Names"
	
	
let
    Source = Sql.Database("db", "database"),
    Storage = Source{[Schema="dbo",Item="Storage"]}[Data],
    #"Alter Column Names" = Table.TransformColumnNames( dbo_OCI_DWH_Inventory_Storage, ( columnName as text ) as text => Text.Combine( List.Transform( Text.Split( columnName, "_" ), each if Text.Length( _ ) >= 4 then Text.Proper( _ ) else _ ), " ") ),
    #"Renamed Columns" = Table.RenameColumns(#"Alter Column Names",{{"Name", "Array"}})
in
    #"Renamed Columns"
	
	
	
let
    Source = Sql.Database("db", "database"),
    Volume = Source{[Schema="dbo",Item="Volume"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(Volume,{"RETRIEVAL_MONTH", "Volume_Trend_Key", "Pool_Trend_Key", "name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"name", "Volume"}}),
    #"Merged Storage Pool" = Table.NestedJoin(#"Renamed Columns",{"Pool_Trend_Key"},Pool,{"Pool Trend Key"},"Pool",JoinKind.LeftOuter),
    #"Expanded Storage Pool" = Table.ExpandTableColumn(#"Merged Storage Pool", "Pool", {"Name"}, {"Pool Name"}),
    #"Merged Storage" = Table.NestedJoin(#"Expanded Storage Pool",{"Storage_Trend_Key"},OStorage,{"Storage Trend Key"},"Storage",JoinKind.LeftOuter),
    #"Expanded Storage" = Table.ExpandTableColumn(#"Merged Storage", "Storage", {"Array"}, {"Array"}),
in
    #"Expanded Storage"

[v-qiuyu-msft]

Hi @Anonymous,

From the Power Query, the error should be occurs by below code as there are two "Array" named columns.

#"Expanded Storage" = Table.ExpandTableColumn(#"Merged Storage", "Storage", {"Array"}, {"Array"}),

Please change the last step of second query to rename column Name as Name1 instead of Array then test again. 

let
    Source = Sql.Database("db", "database"),
    Storage = Source{[Schema="dbo",Item="Storage"]}[Data],
    #"Alter Column Names" = Table.TransformColumnNames( dbo_OCI_DWH_Inventory_Storage, ( columnName as text ) as text => Text.Combine( List.Transform( Text.Split( columnName, "_" ), each if Text.Length( _ ) >= 4 then Text.Proper( _ ) else _ ), " ") ),
    #"Renamed Columns" = Table.RenameColumns(#"Alter Column Names",{{"Name", "Name1"}})
in
    #"Renamed Columns"

Best Regards,
Qiuyun Yu 

[Anonymous]

@v-qiuyu-msft There is only one column named Array, as part of the rename in the Storage table. The expand step is just following the convention where by you specify the column you want in the expand, and then if there is any optional renaming that happens. In this case, it is going from Array to Array. 

This is also only a problem when query folding is in play, if I move the expand step to a point where it happens after query folding then the exact same step is not a problem. 

The error is refering to Name for $Outer which is nothing that I have created, but is only created as part of the query folding done by the tool.

DataSource.Error: Microsoft SQL: The column 'Name' was specified multiple times for '$Outer'.
Details:
    DataSourceKind=SQL
    DataSourcePath=Server;Database
    Message=The column 'Name' was specified multiple times for '$Outer'.
    Number=8156
    Class=16

[Anonymous]

I am having same issues with 'The column 'Beskrivelse' was specified multiple times for '$Outer'.' in Power Query in Analysis Services. I have multiple queries (from Azure SQL DB) that I am trying to merge into one of the main tables, and some of these tables have the column name 'Beskrivelse'. So when I expand the tables and choose to keep these columns (and the source table name), I get this error. But not with all of the tables, just with some of them, which is strange. I of course rename the column straight away to avoid further confusion.

I can try to give more details if it would help.

-Luiza