changing column headers automatically in visual whenever they changed in power query editor

[Anonymous]

My datasource is SQL. I have a table  with columns balancerm Month0, balancerm Month1 up to balancerm Month36. I changed those column headers to balance rm September2023 , balance rm October2023 , etc,. ( Month0 will be the current month and next months will be the preceeding months for the current month) in power query editor dynamically , I have created a 2nd table where balancerm month0 will point to current month name (balancermseptember).

Then I transposed second table and created a list . I have used this list in first table.so the headers got changed according to my requirement.

I closed and apply this ,  the data got loaded on to power bi desktop. I have used this data in a table .

Then whenever the header names got changed i;e Month0 i have changed that to October dynamically  in the backend sql. the data is refreshing in the power query editor correctly. Automatically that changed headers are reflecting in the power query editor.

But the visual in power bi desktop is not getting that column of december as it is appearing in power query editor But it is reflecting here in the data field.

can i get that new headers also to be displayed in the visual automatically . Please help me out.

Thanks in advance.

[Anonymous]

If I follow the process I will be able to get the output like above but i need the output like this as shown below

The column names balancerm month0 should be changed to balancedrm september , balancerm month1 to balance rm october and so on... where the month0 will be always the current month .

If we jump to next month that is october then balancerm month0 should be converted to balancerm octber.

This I  was able to acheive upto power query but the visual in the desktop is not changing with the power query editor. 

Please check the screenshots in my first question.

here the column which is changed i mean balance rm month2 to balance rm december in the backend datasource sql it is reflecting in the power query editor and also the data field  in power bi desktop but not in the visual.

[JoeBarry]

Hi @Anonymous 

You will need to have every value in one column and a column just for dates.

Lets cover everything here. Let create a dynamic date.

Within your table, highlight the Existing col column and go to the ribbon and choose add column. Click on extract and Extract the last two characters and convert to whole number.

Add another custom column called Month. Add this code, just fill in the extra months

if [Month] = 0 then Date.StartOfMonth(DateTime.LocalNow()) else 
if [Month] = 1  then Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()), 1) else 
if [Month] = 2  then Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()), 2) else 
if [Month] = 3  then Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()), 3) else 
if [Month] = 4  then Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()), 4) else 
if [Month] = 5  then Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()), 5) else 

Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()), 36)

 Convert to date

I wouldn't Pivot the columns as Power BI doesn't work like this. I'm presuming the Balance is in the same table. Load the date to the report and Add the Date column in the matrix to the columns. If date hierarchy is activated, add the Year and then the Month to the columns. Add the Balance to the Value field to get teh result you need.

Thanks

Joe

If this post helps, then please Accept it as the solution

[Anonymous]

The above table is my first table where i want to change the colun headers.

This is my second table where u told to add the date customn column. What should be my next step , how will my first table change the column headers from second table . can you help me out to get this.

Thanks, 

Ak**bleep**ha.K

[JoeBarry]

Highlight all columns in the first table and rightclick and click on Unpivot all columns. You should be left with an Attribute and Values columns. You won't need the second table.

Repeat my steps from my first post to get the dynamic date. Load the data the report.

Create a Date Table https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables Then create a one to many relationship between the date column in the date table and the date column in table 1.

Use the date column from the date table in your visuals and add the value column to the visual to get the result you need

[Anonymous]

If I follow the process I will be able to get the output like above but i need the output like this as shown below

The column names balancerm month0 should be changed to balancedrm september , balancerm month1 to balance rm october and so on... where the month0 will be always the current month .

If we jump to next month that is october then balancerm month0 should be converted to balancerm octber.

This I  was able to acheive upto power query but the visual in the desktop is not changing with the power query editor. 

Please check the screenshots in my first question.

here the column which is changed i mean balance rm month2 to balance rm december in the backend datasource sql it is reflecting in the power query editor and also the data field  in power bi desktop but not in the visual.