Forum Discussion

SukanyaGG's avatar
SukanyaGG
Regular Visitor
2 years ago
Solved

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

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 descriptionJANDEC% change

 

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    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.