Just a formula confusion !

[Birinder]

Hello everyone,
Before even asking a question, I would like to thanks all the community members who helped me till date.
Now coming to the question.
As you can see in the below screenshot. I have a databases linked through Azure cloud services. Each dimension contains tables which further contains data in rows and column format.

Now I want to rename some of the columns in the table from a dimension. After researching I found a way to do it through Power Bi DAX measures.

SELECTCOLUMNS (
    "Table",
    "New Name", Table[Old Name],
    "New Name 1", Table[Old Name 1]
)

But I am confused which table to write here in formula. Since it is only picking up the dimensions and not the tables within them. After applying the formula only on Dimensions, it is showing that it can't apply formula on multiple tables, which is quite obvious.
Can anyone clear my confusion. Or if you have any other way. Please feel free to do so.

Thanks guys,
You are the best.

[amitchandak · Accepted solution]

@Birinder , This is a new table script.  You need to create a new table. You can rename a column by right click --> rename /double click

New table option, you will see in table tool, once you select a table

[Birinder · Accepted solution]

@amitchandak 

Actually I forgot to tell you that By anyways I can't do any manipulation in Power Query editor or in "Data model" view. That is the reason I want to create a measure which can change the column names. You have any approach for this.

[bcdobbs · Accepted solution]

Hi,
I'm guessing you're connecting to an instance of Azure Analysis Services?

When you say "Each dimension contains tables which further contains data in rows and column format" are you refering to the folder icons within your dimension table?

Rather than script it all out in DAX you could create a calculated table and just directly reference the original table name. Literally

New Attributes Table = Dim_Other_Attributes_1,

 You can then rename each column in your new table in the GUI as @amitchandak suggests.

HOWEVER

Copying the table will only bring all the data columns. It won't bring the folder structure you can see in the diagram and it won't bring measures across so I'm not sure it's going to do what you want.

Another option might be to leave structure as is and use the rename for visual option as you go along? 

There might also be some scope in turning on the Direct Query for AS and Power BI Dataset preview feature and creating a composite model which would let you rename things.

[bcdobbs · Accepted solution]

Hi,
This is a model connected to an AS instance:

When I drop any column or measure into a visual I get a down arrow next to it with the option to click "Rename for this visual". Option exists for rows, columns and values.

I'm now wondering however if you're actually wanting to rename the data in the column? Eg my visual shows M and F for Gender which are values from within the column. If I wanted to show "Male" and "Female" instead I'd have to create a calculated column or a related table which would again require enabling the DirectQuery for PBI datasets and AS preview feature:

and then clicking:

My understanding is it should work regardless of model size although it is still in preview.

[bcdobbs · Accepted solution]

Don't go into power query but right click the table and click add calculated column:

Renamed Gender =
SWITCH (

    Student[Gender],

    "M", "Male",

    "F", "Female"

)

[Birinder · Accepted solution]

Thanks a lot the formula worked, but it is not showing anything. It is blank. Any suggestion ?

[bcdobbs · Accepted solution]

Hi,
This is a model connected to an AS instance:

When I drop any column or measure into a visual I get a down arrow next to it with the option to click "Rename for this visual". Option exists for rows, columns and values.

I'm now wondering however if you're actually wanting to rename the data in the column? Eg my visual shows M and F for Gender which are values from within the column. If I wanted to show "Male" and "Female" instead I'd have to create a calculated column or a related table which would again require enabling the DirectQuery for PBI datasets and AS preview feature:

and then clicking:

My understanding is it should work regardless of model size although it is still in preview.

[Birinder]

Ok this probably my last reply. 
See @bcdobbs .
I already have direct query connection with the data. It is enabled from Preview features. But when I open Power Query Editor. There is nothing there. So If you were in my position. How you would have changed "F" "M" to Female and Male.

[bcdobbs · Accepted solution]

Don't go into power query but right click the table and click add calculated column:

Renamed Gender =
SWITCH (

    Student[Gender],

    "M", "Male",

    "F", "Female"

)

[Birinder · Accepted solution]

Thanks a lot the formula worked, but it is not showing anything. It is blank. Any suggestion ?

[bcdobbs · Accepted solution]

Switch returns is blank if it can't find a value. Do you want to send over the dax you've used? First thing to check is spelling and spaces match exactly.

[Birinder]

Ohhhh God, It worked finally.
Thank you so much @amitchandak @bcdobbs for all the help you've provided me.
Its really appreciable.

Kudos.

[amitchandak · Accepted solution]

@Birinder , This is a new table script.  You need to create a new table. You can rename a column by right click --> rename /double click

New table option, you will see in table tool, once you select a table

[Birinder · Accepted solution]

@amitchandak 

Actually I forgot to tell you that By anyways I can't do any manipulation in Power Query editor or in "Data model" view. That is the reason I want to create a measure which can change the column names. You have any approach for this.

[bcdobbs · Accepted solution]

Hi,
I'm guessing you're connecting to an instance of Azure Analysis Services?

When you say "Each dimension contains tables which further contains data in rows and column format" are you refering to the folder icons within your dimension table?

Rather than script it all out in DAX you could create a calculated table and just directly reference the original table name. Literally

New Attributes Table = Dim_Other_Attributes_1,

 You can then rename each column in your new table in the GUI as @amitchandak suggests.

HOWEVER

Copying the table will only bring all the data columns. It won't bring the folder structure you can see in the diagram and it won't bring measures across so I'm not sure it's going to do what you want.

Another option might be to leave structure as is and use the rename for visual option as you go along? 

There might also be some scope in turning on the Direct Query for AS and Power BI Dataset preview feature and creating a composite model which would let you rename things.

[Birinder]

Q: I'm guessing you're connecting to an instance of Azure Analysis Services?

A : Yes.

Q: When you say "Each dimension contains tables which further contains data in rows and column format" are you refering to the folder icons within your dimension table?

A: Yes yes, exactly.

Q: Another option might be to leave structure as is and use the rename for visual option as you go along? 
A: But If I rename the visual, it will not rename the columns under it. I've tried it.

Q: There might also be some scope in turning on the Direct Query for AS and Power BI Dataset preview feature and creating a composite model which would let you rename things.

A: The dataset is too large in size to import it and create as a composite model.

My Question: I am way too noob at this, So can you explain in detail what could be done ?

[bcdobbs]

Not by a computer until later but I'm certain you can rename all columns directly in a visual will try and send some screen shots. Am wondering if it's the AMSEG (h) heirarchy that is causing you issues in renaming?

Going the other way composite models doesn't import anything it still directly queries your remote analysis service instance. Have a look at https://powerbi.microsoft.com/en-my/blog/directquery-for-power-bi-datasets-and-azure-analysis-servic...

[Birinder]

@bcdobbs 

Thanks for the replies. Forget the Anseg column, I am not using it BTW. Imagine a non-hierarichal table instead. I will be waiting for your screenshots. Thanks.