Strange circular dependency maybe needs row idnefier

[masplin]

I have an exisitng model which was built very inefficiently by pulling in a whole SQL table then slowly removing different columns. It works fine, but getting slow. I thoguht it woudl be an easy job to replace my initial source query for the whole table with my newly learn SQL.  This all worked fine and to my eye the field names and order was identical in the power query window to the existing query. 

When I tried to refresh I ran into loads of error messages that didn't seem to make any sense.  Some of these werecaused by the refesh changingosmeof my relationships. The rest i delted the offending columns and managed to get the data uploaded inot the data model.  However when I put the various calcuated columns back i endedup with alaod of circular errors that are notpresent in the exisitng model despite the code and relationships being identical.

The only thing I can see has changed is the first column in the working version has Vehicle No as a unique identifier. In my new version for some reason this column is way over on the right and my first column is a status column with duplicated values . I trid adding an index column, but that also is on the right.  If this was excel my first thing would be to set the Vehicle No column as the row identifier.  Does power BI default to using the first column as row identifier and therefore I am now completely screwed as no way to mark a column as row idenfier nor to move the columns in the data view?   i was hoping an index column would be used as the row idenfier, but hasnt solved my problem.

Coudl be somethnig completely different, but i am sure al lmy relationships, data and formulae are identcal.

Appreciate any info on row idenfiers in power BI

Mike

[mwegener]

Hi @masplin ,

I would recommend Power Query, although I consider myself an SQL expert.
Power Query is more readable to other users and can be applied to a variety of sources.
Performance can be improved by the correct order of steps.

Do you know the Power Query option "View native query"?

Yes, SQL is faster, but speed is not everything.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[masplin · Accepted solution]

yes this is all done through Power Query.

I think I found the answer which is quite bizarre.

In my original table the first column say was [Customer No_] in the database. I then renamed this to [Customer No] in PQ.  On upload this is the first column in Data View and I assume Power BI treats this as the row identifier? 

Whne i changed the source query to SQL I thought I would do the renamingon import so wrote

[Customer No_] AS [Customer No]

However Power Bi thinks this is a new column and put it at the end of the data view like it would do if you ammended the Power Query and added a column or if you add calcuated columns.  This is completely mad as the name of the column is identical and created in the source step.  The problem is now my first column in data view is a non-unique column so lots of my calculated columns now have circular errors.  This is not fixable as Power BI does not have the abilty to specifc a column as the row identifier.

Luckily I found the solution. DO NOT rename an SQL column in the SQL SELECT statement. If you SELECT is as [Customer No_] and do theremane in Power Query it sees this as the same column already exisitng and leaves it at the front of Data view. All the formulae work fine.   Probably it's just the column that is the unique row identifier that must not be renamed in SQL.

Took me 3 days to work this out.  Really stupid bug.