topic Headers and Case in DAX Commands and Tips

Headers and Case

Zalexatwork — Tue, 30 Nov 2021 09:27:31 GMT

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,

Re: Headers and Case

OwenAuger — Tue, 30 Nov 2021 11:57:34 GMT

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

Re: Headers and Case

Zalexatwork — Tue, 30 Nov 2021 12:21:14 GMT

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

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

Re: Headers and Case

OwenAuger — Tue, 30 Nov 2021 21:41:54 GMT

You're welcome 🙂

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

let

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

Re: Headers and Case

Zalexatwork — Wed, 01 Dec 2021 08:11:44 GMT

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.