How to get column name dynamically in Power BI?

Question

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.

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

I think, you can use DAX function to solve this.

pcghose
– pcghose

2019-11-14 06:50:12 +00:00
Commented Nov 14, 2019 at 6:50 — pcghose
– pcghose, Commented Nov 14, 2019 at 6:50
@ProtapGhosh the same i want to know.

Shahab Haidar
– Shahab Haidar

2019-11-14 06:55:04 +00:00
Commented Nov 14, 2019 at 6:55 — Shahab Haidar
– Shahab Haidar, Commented Nov 14, 2019 at 6:55

Andrey Nikolov · Accepted Answer · 2019-11-14 06:59:29Z

1

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.

answered Nov 14, 2019 at 6:59

Andrey Nikolov

13.5k3 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shahab Haidar Over a year ago

Hi @Andrey Nikolov , The column name was successfully concatenated but it is returning the column name in values too. i want to retrieve the column value by concatenating the column name. Please see the result . ibb.co/dbYm6Hd

Andrey Nikolov Over a year ago

@ShahabHaidar That's why I told you to unpivot the table.

pcghose · Accepted Answer · 2019-11-14 07:01:37Z

0

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" )

answered Nov 14, 2019 at 7:01

pcghose

1792 silver badges19 bronze badges

1 Comment

Shahab Haidar Over a year ago

Hi @Protap Ghosh , The column name was successfully concatenated but it is returning the column name in values too. i want to retrieve the column value by concatenating the column name. Please see the result . ibb.co/dbYm6Hd

Collectives™ on Stack Overflow

How to get column name dynamically in Power BI?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related