rename/auto update column name when auto update excels data.

[SukanyaGG]

Hi, 

I have created merged data using 2 month data, where each month new month data will get udpated. 

as in merged data set there only month column which contains both month data. 

I wanted different column so I have used this formula -

JAN = IF('NNI ALL'[Month]="January'2024",'NNI ALL'[QTY],BLANK())

DEC = IF('NNI ALL'[Month]="December'2023",'NNI ALL'[QTY],BLANK())

and created new two column's. 

but now when im auto updating data in each month, how can I make column name update automatically and the formula referance for new month name. Bold highlighted is the parts. 

relationship between both month data set is = Many to Many, Both

Let me know any other way I can do this ? 

Im doing all of these to get below result in power BI table. 

Product description

JAN

DEC

% change

[Ashish_Mathur]

Hi,

You should try this

1. Ensure that the Month column in your Data Table is of the Date data type.  If not, then please do so in the Query Editor
2. Create a Calendar Table with calculated column formulas for Year, Month name and Month number
3. Sort the Month name column by the Month number column
4. Create a relationship (Many to One and Single) from the Month column of your Data Table to the Date column of the Calendar Table
5. To your visual, drag Year and Month name from the Calendar table
6. Simplify your measure to

Measure = sum('NNI ALL'[QTY])

Hope this helps.

[v-cgao-msft · Accepted solution]

Hi @SukanyaGG ,

Dynamic column names are not currently supported in DAX, this need done in PowerQuery. Please create a new empty query in PowerQuery and replace the following code in the advanced editor and follow the steps there:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+rDoAgFADQf7mZAJd3dwZ/AQngiBrYCP690zENcvNJJwSYS64t1XNtnKNFjgoYaA2REWYMbdbS5hxt3j+2pONHQpCESJKUJClFUm9PZSt7LvU1+bWH1ttD6+2h9fbQxB2PFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, QTY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"QTY", Int64.Type}}),
    #"Added Date" = Table.AddColumn(#"Changed Type", "Date", each let 
monthyearlist = Text.Split([Month],"'"),
datetext = monthyearlist{1} & "-" & monthyearlist{0} & "-" & "1",
datevalue = Date.FromText(datetext)
in 
datevalue),
    #"Top2 Date" = List.MaxN(List.Distinct(#"Added Date"[Date]),2),
    YearMonth1 = List.Distinct(Table.SelectRows(#"Added Date",each [Date]=#"Top2 Date"{0})[Month]){0},
    Month1 = Text.Start(#"YearMonth1",3),
    YearMonth2 = List.Distinct(Table.SelectRows(#"Added Date",each [Date]=#"Top2 Date"{1})[Month]){0},
    Month2 = Text.Start(#"YearMonth2",3),
    Custom1 = Table.AddColumn(#"Changed Type",#"Month2",each if [Month] = #"YearMonth2" then [QTY] else null),
    Custom2 = Table.AddColumn(Custom1, #"Month1",each if [Month] = #"YearMonth1" then [QTY] else null)
in
    Custom2

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group