Power Query: how can I make power query tolerate columns with the same name?

Question

I have a table with several column having the same name. This columns is updated and provided regularly to power bi.

The columns has several columns with the same name such as "Result", "Result" and do so on...

However, Power BI adds each time in an automated way a number after my columns.

When I try to "force" power bi not to have a number, I get the following message "The name "Result" is already used for a column..."

How could I change this?

The only way would be for the people using my file to extract the data and correct the name manually in excel...which is not great

Answer 1

You cannot edit this behavior, PowerBI needs an unique identifier to reference the data, therefore the column name must be unique within a table (the complete identifier is given by table + column), otherwise the tool won't be able to reference the data.

This rule usually applies to any tool that manages data and sometimes to the data themselves (that's up to the format though). How can the tool get data from "Result" if more than one column has this identifier? which is the right one? The tool does not know and based on the context can give you an error or will fix this issue itself by making the names unique.

Note that also excel will append numbers to the columns (with the same name names) if you put the data in a proper table (insert-> table), in fact, an excel sheet can be considered unstructured free data, meanwhile, an excel table will enforce the data structure. Most tools (like PowerBI) will also enforce data types.