If columnname is already been used then use another columnname

[KBE]

Hello,

thought it would be something simple and in my memories I thought it worked already. About 12 month ago I made a query to restructure offered excel formats from different suppliers to a standard format. Now some formats use a columnname with ADRESS and some have three different columnnames with STREET, NUMBER and LETTER and some use both. 

if the format was offered with the three column seperate I'm combining them with the columnname ADRESS. Now it happens in occasions two columnames are the same and powerquery gives an error. So to bypass the error I used the following:

try Table.AddColumn(#"PreviousStep", "ADRESS", each ([STREET]&[NUMBER]&[LETTER])) otherwise Table.AddColumn(#"PreviousStep", "NOTTHEADRESS", each ([STREET]&[NUMBER]&[LETTER]))

Am I missing something why this doesn't work.

Thanks in advance!

[wdx223_Daniel · Accepted solution]

=Table.AddColumn(#"PreviousStep",if List.Contains(Table.ColumnNames(#"PreviousStep"), "ADRESS") then "NOTTHEADRESS" else "ADRESS",each Text.Format("#[STREET]#[NUMBER]#[LETTER]",_))

[KBE]

Thanks a lot! it works and also look like the proper way to do so. Can add this to my repertoire. 

[v-jingzhang]

Hi @KBE 

I tried your M code, it works well with my sample data as below. 

One possible cause of error is the data type of three columns. & is used to concatenate Text values. If one value is in other data types (e.g. NUMBER column is in Integer type in below table), it will return error. So please check the data types of columns [STREET], [NUMBER] and [LETTER] in your table. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[KBE]

Hello, 

thank you for the effort. In the previous step I changed the types, there is probably still something wrong with my doing, but I appriciate the help.

Kind Regards,