Prevent Power BI from throwing errors when I change Excel columns?

[Anonymous]

I'm new to Power Bi, but I'm experienced with Tableau. Once thing I'm having trouble with is whenever I delete or rename a column in Excel and try to refresh the data source, Power Bi freaks out.

In Tableau if I were to do the same thing, it wouldn't care, it would just update the column names or leave out the columns I deleted. It would only give me an error if I had referred to a column that's no longer there in one of my formulas. 

Why doesn't Power Bi automatically update if I rename or delete a column? And is there a way for me to tell it to stop caring and just refresh the data source? 

Thanks for your time. 

Update: these gifs should make it easier to understand what I mean

VS

[amitchandak]

@Anonymous , What I understood is that you deleting and renaming in power bi

I do not think power BI does that. You have to change rename and delete a column in power BI, not in excel.

Power BI has a very strong data transformation/Edit query module. You should use that. In this same module, you can use M language and get you modified excel

Refer: https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data

https://learn.radacad.com/course/advanced-data-transformation-and-preparation-with-power-query-in-power-bi/

https://radacad.com/pivot-and-unpivot-with-power-bi

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Refer

https://support.microsoft.com/en-gb/office/add-a-column-from-an-example-power-query-cd82c570-8da7-4d70-91a1-3827b5995eab

[Anonymous]

Thanks for directing me to some workarounds. Based on your response, it takes much more time to work with different versions of Excel files in Power BI compared to Tableau.

For example, right now I'm working on a project using weekly snapshots of Salesforce Opportunities. The columns are different depending on the week. Are you telling I'll have to manually explain to Power BI each time a field is different from the previous version? That could add up to hundreds of steps! In Tableau, I wouldn't have to life a finger as long as existing formulas can keep referencing columns with the same name.

This should make it easy to undersand what I mean

Compare that to this

[v-lid-msft]

Hi @Anonymous ,

Please check the Advanced Edtior in Power Query Editor (Click "Transform Data"), we make a simple to explain the query in power bi, firstly, we import data from excel file, the entire query will be something like it:

let
    Source = Excel.Workbook(File.Contents("D:\Temp testing only\test.xlsx"), null, true),
    Table_Table = Source{[Item="Table",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table_Table,{{"Column A", Int64.Type}, {"Column B", Int64.Type}, {"Column C", Int64.Type}})
in
    #"Changed Type"

you can find the third step "#"Changed Type"" use the Column A, B, C, if you use more transformat in power query editor, it may use those column many times, the simple way to delete the "column B" is delete it in query, such as following

let
    Source = Excel.Workbook(File.Contents("D:\Temp testing only\test.xlsx"), null, true),
    Table_Table = Source{[Item="Table",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table_Table,{{"Column A", Int64.Type}, {"Column C", Int64.Type}})
in
    #"Changed Type"

Power BI will use column name as refer in queries, if you use column to transform and delete it in origin data source, we need to change the query to make it work again.

But if columns in your data source changed frequently, we can also use unpivot or other function to map them to common name in second or third steps, depends on different scenario. Please also refer to those threads:

https://community.powerbi.com/t5/Desktop/Mapping-columns/td-p/663161

https://community.powerbi.com/t5/Desktop/Dynamic-Table-Data-Mapping/td-p/197549

Best regards,

[Anonymous]

Thanks Dong. I'll use that as a workaround. However, based on your comment, there isn't a way to make Power BI do this automatically. 

[v-lid-msft]

Hi @Anonymous ,

As an addition, if we do not use column names in any steps, we can delete/change them in data source. But the field of charts or dax also refer the column names, so we recommand use unpivot or map them to common column name.

Best regards,