dates as column names

[bhill024]

I am pulling in data from a SQL server database  that is stored vertically (one row per quarter) and pivoting it so I have the  dates as columns. After pivoting, it looks like this:

[ID]  [Variables] [2016:2] [2016:1] [2015:4] [2015:3]

I am then performing calcuclations (difference, percetages, etc.).

What happens when I pull it next quarter and the columns become:

[ID] [Variables] [2016:3] [2016:2] [2016:1] [2015:4]

? Will my calculations be done correctly, or will the formulas be hard-coded based on the previous dates (columns)? What I need is a way to reference the columns by their position in the dataset, not by their name. Its pretty easy in VBA and R, but I cannot find a solution in DAX.

Thanks!

[KGrice · Accepted solution]

Hi @bhill024. I can't pull from SQL Server right now to confirm, but I don't think the column names will change at all. This is the case if I create a table in Excel, import it, and then go back to the Excel file and change a column header. The table in Power BI retains the original column name.

The good news is your calculations won't break, but of course your column headings will be misleading. Is there a requirement to dynamically name the columns? If not, a simple workaround would be to alias the columns as something like CurrentPeriod, CurrentPeriod-1, CurrentPeriod-2, CurrentPeriod-3. If you want to make it more obvious what the corresponding dates are in your report, you could pull the dynamic headings into a separate table as row values, and display them on the report.

[v-haibl-msft · Accepted solution]

@bhill024

The columns name should be able to change after you change the quarter in your data source. Then if your calculations reference these columns by name, they will not work anymore.

Based on my testing, the column position will also change after we click Refresh in Power BI Desktop as below. So the workaround provide by KGrice should be a good choice.

Best Regards,

Herbert

[KGrice · Accepted solution]

Hi @bhill024. I can't pull from SQL Server right now to confirm, but I don't think the column names will change at all. This is the case if I create a table in Excel, import it, and then go back to the Excel file and change a column header. The table in Power BI retains the original column name.

The good news is your calculations won't break, but of course your column headings will be misleading. Is there a requirement to dynamically name the columns? If not, a simple workaround would be to alias the columns as something like CurrentPeriod, CurrentPeriod-1, CurrentPeriod-2, CurrentPeriod-3. If you want to make it more obvious what the corresponding dates are in your report, you could pull the dynamic headings into a separate table as row values, and display them on the report.

[Heman257]

" If not, a simple workaround would be ... "

... but if yes? I want to change column name in data source and I want to reproduce this name to column name.

The point is to display names for columns independent on headers on data sets.

Table header:

Column1,Column2,Column3 

Something like Column1=Year

If i pick Column1 for filtr, filtr displays "Column" name, but i want to display "Year".

Thanks for reply

HAS