Changing column names in SQL database causes them to disappear from visuals

[arelf27]

Hi. We created a bunch of reports with various visuals that have been published to the Cloud. We're connecting to SQL Server Views via Import method. The issue now is that we want to rename a bunch of fields in underlying Views directly in SQL Server (not in Power BI.) When I changed a field and refreshed PowerBI .pbix the graphs displayed error. When I clicked to fix error the graphs came back but the re-named field was now missing. So essentially the field got renamed correctly, however I have to manually re-add it to the visual.. Now imagine if we want to change 10 column names in a SQL view.. I don't want to spend hours on end fixing visuals and also not sure how to know which visuals had which fields... It's like I'd have to take before and after of each visual and change accordingly.. My quesion is, is there a graceful way to change a column name in underlying SQL view, table, etc. and not brake the visuals? Is there a way to re-map it somewhere within PowerBI so it knows to use the new field name in place of the old field name? And/or what's the best design for the future? I'm affraid that down the road if we need to change even one column name there will always be this issue of how do I do it wihout loosing the work? Thank You

[v-jiascu-msft]

Hi @arelf27,

Could you please tell me if your problem was resolved? Please mark the proper answer if it worked. That will be a help to the others.

Best Regards!
Dale

[Anonymous]

If the columns change in SQL, Power BI doesn't know that the old columns weren't deleted or that the new columns are the same data.  What you need to do instead is getting into your Edit queries and rename those columns yourself as part of the transformation steps.  As long as the column names are the same when it leaves the Edit Queries, your graphs should be happy.

[arelf27]

I understand that I can rename columns in Edit Query in PowerBI and there will be no issue in the visuals but that's not my question. I want to re-name all columns in my underlying view in SQL. At this point I see many advantages to maintaining clean names in SQL view itself and not re-naming in PowerBI (esp. if the view will be re-used, every single solution would have to start with re-naming columns step, etc. which doesn't work.) Are there any tricks like writing DAX or M, etc. to trick PowerBI?

[v-jiascu-msft]

Hi @arelf27,

Could you please try this workaround? The main idea is "demote the headers and delete them, then rename all the columns to get new fixed headers".

1. "Use Headers as First Row" (1);

2. "Remove Rows" top 1 (2);

3. Rename a column. (in order to see the code);

4. Open "Advanced Editor" (3);

5. Get all the headers by sql or what means available;

select top 0 * from dbo.DimProduct;

6. Copy the headers to excel and transfer to one column;

7. Use these two formulas to get the M code. (partial).

=CONCATENATE(",{""",I8,""",""",J8,"""}")

=CONCAT(K8:K39)

,{"Column1","ProductKey"},{"Column2","ProductLabel"},{"Column3","ProductName"},{"Column4","ProductDescription"},{"Column5","ProductSubcategoryKey"},{"Column6","Manufacturer"},{"Column7","BrandName"},{"Column8","ClassID"},{"Column9","ClassName"},{"Column10","StyleID"},{"Column11","StyleName"},{"Column12","ColorID"},{"Column13","ColorName"},{"Column14","Size"},{"Column15","SizeRange"},{"Column16","SizeUnitMeasureID"},{"Column17","Weight"},{"Column18","WeightUnitMeasureID"},{"Column19","UnitOfMeasureID"},{"Column20","UnitOfMeasureName"},{"Column21","StockTypeID"},{"Column22","StockTypeName"},{"Column23","UnitCost"},{"Column24","UnitPrice"},{"Column25","AvailableForSaleDate"},{"Column26","StopSaleDate"},{"Column27","Status"},{"Column28","ImageURL"},{"Column29","ProductURL"},{"Column30","ETLLoadID"},{"Column31","LoadDate"},{"Column32","UpdateDate"}

8. Add or edit the code like it in the picture. Pay attention to the format of M.

9. Finally we get the right and fixed headers, and refresh won't influence the visual.

10. Check if the headers are correct.

Please have a try. If you encounter any problem, please feel free to ask. A snapshot will be great.

Best Regards!

Dale