cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ovetteabejuela Established Member
Established Member

PowerQuery Question: Renaming Header

Hi,

 

I've got a data source that is in Chinese, the headers are in Chinese but I already have the equivalent in English but how do I rename the header in Advanced Mode that

 

If <header-in-chinese_1> then <header-in-english_1>

If <header-in-chinese_2> then <header-in-english_2>

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: PowerQuery Question: Renaming Header

MissingField.Error would halt the input process.

 

I don't know if there is a standard approach.

My suggestion would be to create 3 queries, like:

  1. ColumnRenames with From and To column Names
  2. GetInput: getting the input and renaming the columns with MissingField.Ignore
  3. CheckInput: select all records from ColumnRenames of which the To name does not exist in GetInput (should be empty).

You may then include CheckInput in some visual as an alert.

 

Below some sample code you can copy/paste in an empty pbix file to see how t works.

 

ColumnRenames:

let
    Source = #table(type table[From = text, To = text],{{"Chinese 1", "English 1"},{"Chinese 2", "English 2"},{"Chinese 3", "English 3"}})
in
    Source

 

GetInput (I just created some data in a table with 2 columns):

let
    Source = {1..10},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Chinese 1"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Chinese 2", 0, 1),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Index",Table.ToRows(ColumnRenames), MissingField.Ignore)
in
    #"Renamed Columns1"

 

CheckInput (just a oneliner, without let .. in):

Table.SelectRows(ColumnRenames, each not List.Contains(Table.ColumnNames(GetInput),[To]))
Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
ovetteabejuela Established Member
Established Member

Re: PowerQuery Question: Renaming Header

Let me change my question...

 

Do I have to evaluate if the header exists? Like what the header name has changed? How do we handle this properly?

MarcelBeug Super Contributor
Super Contributor

Re: PowerQuery Question: Renaming Header

You can add a third argument MissingField.Ignore to Table.RenameColumns, like:

 

= Table.RenameColumns(PreviousStep,{{"Chinese name", "English name"}}, MissingField.Ignore)

 

Alternatively, you can use MissingField.UseNull, then the column with the English name will be created with null values, in case the column with the Chinese name does not exist.

The default is MissingField.Error

Specializing in Power Query Formula Language (M)
ovetteabejuela Established Member
Established Member

Re: PowerQuery Question: Renaming Header

That is very much helpful @MarcelBeug and thank you very much. I do have more question though...

 

If this is a regular task for someone already, would there be a way to be notified that an error has occured or a Header name has gone missing? If I use the default MissingField.Error would that halt the import process?

 

I will try it now but maybe what I wanted to know is if there is an error handling best suited for a scenario like that.

MarcelBeug Super Contributor
Super Contributor

Re: PowerQuery Question: Renaming Header

MissingField.Error would halt the input process.

 

I don't know if there is a standard approach.

My suggestion would be to create 3 queries, like:

  1. ColumnRenames with From and To column Names
  2. GetInput: getting the input and renaming the columns with MissingField.Ignore
  3. CheckInput: select all records from ColumnRenames of which the To name does not exist in GetInput (should be empty).

You may then include CheckInput in some visual as an alert.

 

Below some sample code you can copy/paste in an empty pbix file to see how t works.

 

ColumnRenames:

let
    Source = #table(type table[From = text, To = text],{{"Chinese 1", "English 1"},{"Chinese 2", "English 2"},{"Chinese 3", "English 3"}})
in
    Source

 

GetInput (I just created some data in a table with 2 columns):

let
    Source = {1..10},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Chinese 1"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Chinese 2", 0, 1),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Index",Table.ToRows(ColumnRenames), MissingField.Ignore)
in
    #"Renamed Columns1"

 

CheckInput (just a oneliner, without let .. in):

Table.SelectRows(ColumnRenames, each not List.Contains(Table.ColumnNames(GetInput),[To]))
Specializing in Power Query Formula Language (M)

View solution in original post

ovetteabejuela Established Member
Established Member

Re: PowerQuery Question: Renaming Header

@MarcelBeug, I have yet to try but I can see where you're going and I already think that this is an awesome approach. I will try and get back to this thread and will flag it as a solution when everything works.

 

Thanks again for the elaborate assistance.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 301 members 2,795 guests
Please welcome our newest community members: