Headers and Case

[Zalexatwork]

Hello all,

I have seen this question asked but the answer does not work for me so I am opening this probably very simple subject.
My database changed and all my tables now have lower case headers instead of previous upper case, which means my queries do not work on the new tables.
The headers I want to change are the headers of the database (not headers I manually promoted to headers), so the "transpose , upper case, then re transpose" solution does not work as the headers then disappear, and is not practical for me anyways.

I would like to implement a function directly in advanced editor to do so(which I have never used before so maybe a syntax issue)
My base code after acquisition for the table is this :

let
Source = PostgreSQL.Database("Servername", "datasourcename"),
public_Mytable = Source{[Schema="public",Item="Mytable"]}[Data]in
public_Mytable

I try to apply the answer from:
Solved: Programmatically change the case of headers (from ... - Microsoft Power BI Community

to make this :

let
Source = PostgreSQL.Database("Servername", "datasourcename"),
public_Mytable = Source{[Schema="public",Item="Mytable"]}[Data]

Source = #table(2,{{1,2}}),                   

HeadersUpperCase = Table.TransformColumnNames(Source,Text.Upper)

in
public_Mytable

 Im guessing the issue is on the Source = #table(2,{{1,2}}),   which I have copied litterally from the answer and probably does not apply as is, but i cant find syntax explanation in order to solve this.
I know its a small thing but i would appreciate explanation on syntax + solution to my problem

Regards,

[OwenAuger · Accepted solution]

Hi @Zalexatwork 

I would say the code you need is:

let
  Source           = PostgreSQL.Database("Servername", "datasourcename"),
  public_Mytable   = Source{[Schema = "public", Item = "Mytable"]}[Data],
  HeadersUpperCase = Table.TransformColumnNames(public_Mytable, Text.Upper)
in
  HeadersUpperCase

The HeadersUpperCase step needs to reference the step that produced the original table, i.e. public_Mytable

And after the final "in", you should reference the step name that produced the result you want to return, i.e. HeadersUpperCase.

Oh and that #table(...) step wasn't needed, as you suspected.

Hopefully that works!

Regards,

Owen

[OwenAuger · Accepted solution]

Hi @Zalexatwork 

I would say the code you need is:

let
  Source           = PostgreSQL.Database("Servername", "datasourcename"),
  public_Mytable   = Source{[Schema = "public", Item = "Mytable"]}[Data],
  HeadersUpperCase = Table.TransformColumnNames(public_Mytable, Text.Upper)
in
  HeadersUpperCase

The HeadersUpperCase step needs to reference the step that produced the original table, i.e. public_Mytable

And after the final "in", you should reference the step name that produced the result you want to return, i.e. HeadersUpperCase.

Oh and that #table(...) step wasn't needed, as you suspected.

Hopefully that works!

Regards,

Owen

[Zalexatwork]

Hello,
Thanks Owen, it worked.
As suspected it was primarily a syntax issue. So if i understand correctly:

let
  Source           = PostgreSQL.Database("Servername", "datasourcename"),
  public_Mytable   = Source{[Schema = "public", Item = "Mytable"]}[Data],
  HeadersUpperCase = Table.TransformColumnNames(public_Mytable, Text.Upper)
in
  HeadersUpperCase

Line 1, 2 and 3 allow for the program to understand the source to which i want to apply my query
line 4 is the function I apply

line 5 lets it understand that this function should be created in a new step called 'headersuppercase'

So were I to apply a new function I would either do this :

let
Source = PostgreSQL.Database("Servername", "datasourcename"),
public_Mytable = Source{[Schema="public",Item="Mytable"]}[Data]                

HeadersUpperCase = Table.TransformColumnNames(Source,Text.Upper)
FUNCTION 2 = Table.Function(arguments)

in HeadersUpperCase

to get upper case AND function 2 applied in the Headersuppercase step, or :

let
Source = PostgreSQL.Database("Servername", "datasourcename"),
public_Mytable = Source{[Schema="public",Item="Mytable"]}[Data]                

HeadersUpperCase = Table.TransformColumnNames(Source,Text.Upper)

in HeadersUpperCase

let
Source = PostgreSQL.Database("Servername", "datasourcename"),
public_Mytable = Source{[Schema="public",Item="Mytable"]}[Data]   
FUNCTION 2 = Table.Function(arguments)
in Function2


To have two functions applied on the same table in two different steps,
Im guessing below would give the same result:

let
Source = PostgreSQL.Database("Servername", "datasourcename"),
public_Mytable = Source{[Schema="public",Item="Mytable"]}[Data]                

HeadersUpperCase = Table.TransformColumnNames(Source,Text.Upper)

in HeadersUpperCase
FUNCTION 2 = Table.Function(arguments)
in Function2

[OwenAuger · Accepted solution]

You're welcome 🙂

I think the easiest way to describe the structure of the M code for a typical query is:

let

   <List of variable definitions (comma-separated), that can refer to each other>

in

   <Variable containing the result to return>

Typically the list of variables after let represent "steps" in a sequence (especially if generated by the UI), so each will reference the previous one. You will see this list in the Applied Steps panel on the right of the Power Query editor.

I made a slight correction to your code where you applied an additional function, and colour-coded the step names to show how each step references the previous one:

Regards,

Owen

[Zalexatwork]

Very helpful and very clear. I worked with an example with a few generated steps and it makes a lot of sense. Just very different from the VBA structure Im used to.