Forum Discussion

arelf27's avatar
arelf27
Helper II
9 years ago

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

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 o...
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    arelf27
    Helper II
    9 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      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