How to change columns names automatically in Power BI desktop?

[Anonymous]

Hi,

I have table which contains these columns (content is an example):

Table name	Column name	Display text english	Display text name german	Description english	Description german
dbo.Food	fruits_col	fruits	obst	fleshy seed-associated structures of a plant that are sweet or sour	fleischige samenassoziierte Strukturen einer Pflanze, die süß oder sauer sind

I need to create report in to languages with translation from this table. I have other tables from SQL database which names of columns I will connect/relate with content from column "column name" from table above. Problem is that column names in other tables are horisontal and "column name" column from table above is vertical. Pivot option need to much manually work. I need that Power BI change column names automatically. Maybe it can be achived by DAX?

Anna

[edhans]

Hi @Anonymous , 

One issue is I don't think there is a way for a query in Power Query to know the name of the table it has pulled. It gets the table of course, but that data isn't exposed to my knowlege in a way subsequent steps can access it. Furthermore, the "table" Power Query is now working with has its own name. If they are the same it is just because you named them the same. They are not related. So it could be called Query1. And there is no way for PQ to know that either. No function will return that to my knowledge.

That said, you can use one table's data to rename the columns in another. See this code - there are two tables here.

This is your first table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcxBCsJADAXQq4RZV4/hWnBZiqQzqQ3WiSQZpL2MF3HXizmD4Cbk/4TX9yGNcjyJpNCFSQu7XaMs/1AXGc1bXsjmFYwoHdBMIqNTAnMt0YuSgUyA8FwwO/iMDqgE9iJyEAWToj+FLc58qyd8UG7SxkzqBJdK3RuVgTiTwnmq2EYdJK7v+2d/g6TaG5Y2OacwDF8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Table name" = _t, #"Column name" = _t, #"Display text english" = _t, #"Display text name german" = _t, #"Description english" = _t, #"Description german" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Table name", type text}, {"Column name", type text}, {"Display text english", type text}, {"Display text name german", type text}, {"Description english", type text}, {"Description german", type text}})
in
    #"Changed Type"

Which looks like this:

 

then I created this simple table called dbo Food with this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fruits_col = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"fruits_col", Int64.Type}}),
    #"Change Column Name" = 
        let 
            varOldName = Table.SelectRows(Table, each [Table name] = "dbo.Food")[Column name]{0},
            varNewName = Table.SelectRows(Table, each [Table name] = "dbo.Food")[Display text english]{0}
        in
        Table.RenameColumns(#"Changed Type", {{varOldName, varNewName}})
in
    #"Change Column Name"

Which looks like this:

The below code (shown in the M code above) will rename the column from "fruits_col" to "fruits"

You would need to be able handle this in DAX though. You cannot drop "Fruits" into a visual and then change a setting thinking that "Obst" will just go into that same visual. It won't. DAX will see Fruits no longer exists and throw an error. And any dynamic naming changes can also impact Power Query further down in steps if other steps reference column names unless you also make those dynamic - which is definitely possible, but will be very very tedious.

This would be more useful as a way to control column renames with a table, but not dynamically switch between languages. 

You can see the PBIX file here.

[Anonymous]

Hi @edhans,

Thank you very much. I will test your solution.

Best regards, 

Anna

[v-lionel-msft]

Hi @Anonymous ,

Maybe you can do like this.

1. Use 'Unpivot Columns' feature to transform the 'dbo.Foods' table.

2. Create relationship between 'dbo.Foods' table and 'Description' table.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lionel-msft]

Hi @Anonymous ,

Suppose you have the following table. What kind of table do you want to get through transformation?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lionel-msft,

I have following table:

I will create many raports from diverse tables from SQL database which can automatically take values from "Display text" columns in both languages and values from "Descripton" columns in both languages which are related to column names from other tables from SQL database. The best is change names of columns automaticaly in both languages og iut can be also added as text box or card visualisation. An exaple of table:

 Tables which I will use can have even 150 column. Therefore pivoting and unpivoting is diffilcult.

Best regards,

Anna

[Greg_Deckler]

Sorry @Anonymous not sure I followed that completely but I think you have three options:

1. Unpivot the columns in your other tables

2. Pivot that column in that table

3. Use DAX measure to essentially form the relationship between tables

It is difficult to say which approach is best as not sure of the larger context in which this issue resides.