Forum Discussion
Power Query | Batch Replace String
In that case you can use this code:
= Table.TransformColumnNames(Table1, each try Translations{[Foreign = _]}[English] otherwise _)It searches each column name in the Translations table and - if found - replaces it with English; if not found then leave it as is.
Interesting inputs, first off apologies for not doing a great job in presenting my case - I forgot to highlight that in this particular data I only need to translate the Foreign Language found in the headers (though there are some entries in the data itself, but I don't need to translate them)
Anonymous, that is a neat approach I actually did that as well:
1. I demoted the header into the first row,
2. I merged the raw data and the translation table
3. Yes, indeed I got the translations but now the problem is -- how do I gt that over the first row so that if I transpose back I can promote the english version to the headers.
Your's is very interesting, I will perform that as well and see what I can learn from it. Though at my level I can't read the code the same way as reading a sentence, I can see there there is an add column function which make me think that it might not work because again I failed to indicate that I am working on the headers and that was my bad.
I think this one could work for me, but what do I do if I specify a column only, say Column1 since my first steps was to expose the headers and transpose it ready to be replaced/translated.
Thank you'all for the input, very much appreciated.
Basically, this is how the raw data looks like:
| Foreign Language Header 1 | Foreign Language Header 2 | Foreign Language Header 3 | Foreign Language Header 4 | Foreign Language Header 5 | Foreign Language Header 6 |
| Data | Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data | Data |
In that case you can use this code:
= Table.TransformColumnNames(Table1, each try Translations{[Foreign = _]}[English] otherwise _)It searches each column name in the Translations table and - if found - replaces it with English; if not found then leave it as is.
As usual from you MarcelBeug an excellent knowledge share, Another gem in my M treasure chest..
Thank you!!!