Inspired by Ruth Pozuelo from Curbal latest video – https://www.youtube.com/watch?v=jlogBrcYZwc&t=2s where she shows an excellent tip on how to change column headers to upper or proper case –
And the trick is to use the second argument in the Table.TransformColumnNames – and for instance use Text.Upper to sett all names to capitals.
You can also use
I decided to check if I could use some of the other text functions to do even more 🙂
So why not see if we could give each column the name of the index number they have in the query.
And it turns out that it’s possible
If you add the following step
= Table.TransformColumnNames(Custom3, each “”)
The each statement will to the renaming for each column and name it to an empty string – and if you have more than one column with the same name it will get a index number added as a suffix – so column 2 will be name empty string + 1 and so on.
In order to rename the columns to start with 1 instead of an empty string we can use the TransformColumnNames once more but this time to add 1 to each column names – but as column names is text we need to convert it to a value and back to text again
= Table.TransformColumnNames(#”Custom1″, each if _ = “” then “1” else Text.From(Value.FromText(_)+1))
And now we can refer to cells in our table using coordinates
Hope you find this useful and happy querying 🙂