How to get column name dynamically in Power BI?

0
votes

I have an excel sheet where users maintain data format month wise. every new month the add a column with some prefix and maintain the format. like for this month they will add the column name "OCT_Data_Format". Please see the below screenshot.

enter image description here

Again in December they'll add a new column with new name "DEC_data_format".

I want to add a custom column with name "DataFormat" and use the value of new column in every month.

Requesting you to please let me know if there is any function available in power bi to concatenate the month name with prefix "_data_format".

like we do in programming. ex. [function.getcurrentmonthname + "_data_format"].value

2
powerbidaxpowerquery
I think, you can use DAX function to solve this. – Protap Ghosh
@ProtapGhosh the same i want to know. – Shahab Haidar

2 Answers

1
votes

Concatenating the name of the column based by current month "name" (it's not the full name to be precise) can be made like this (in DAX):

SWITCH (
    MONTH ( TODAY() ),
    1, "Jan",
    2, "Feb",
    3, "Mar",
    4, "Apr",
    5, "May",
    6, "Jun",
    7, "Jul",
    8, "Aug",
    9, "Sep",
    10, "Oct",
    11, "Nov",
    12, "Dec"
) & "_Data_Format"

But what you need is to unpivot your table and get all months data into two columns - Attribute and Value, where the values in attribute will be the names of the old columns and you can use standard filtering to find what you are looking for.

0
votes

This may solve your problem:

CONCATENATE( SWITCH( [MONTH(NOW())], 1, "JAN", 2, "FEB", 3, "MAR", 4, "APR"  
               , 5, "MAY", 6, "JUN", 7, "JUL", 8, "AUG"  
               , 9, "SEP", 10, "OCT", 11, "NOV", 12, "DEC"  
               , "Unknown month number" ), "_data_format" )
  1. https://docs.microsoft.com/en-us/dax/concatenate-function-dax
  2. https://docs.microsoft.com/en-us/dax/month-function-dax
  3. https://docs.microsoft.com/en-us/dax/switch-function-dax