Help with promoted headers issue in Query M

[Nun]

Hello,

I have a source in excel, where there is a column that the head changes according to the month. Example: if the month is August, Aug appears as the head of the column, if the month is September, the head of the column is Sep, if it is October, it changes to Oct.

Only 3 values can contain the head of the column. In query M how can I make Table.TransformColumnTypes (# "Promoted Headers", {{"CHECK", type text}, {"Month", type any}, consider the value that is in Excel and give no error: Expression. Error: The column 'Aug' of the table wasn't found( because in Excel there is Sep for example)

Thanks!

[Nun · Accepted solution]

Sure!

the excel file has a column where the the firs row is empty, the second has month (in the month cell there is a formula, if =month(today()) = 8, "Aug",if month(today())=9, "Sep","Oct")) so

colum1

empty

Aug (because the month is 8, but next month the value change to "Sept")

10

20

30

in query M, I removed the first row, and promote the 2 row as header:Table.TransformColumnTypes(#"Promoted Headers",{, {"Aug", type any}.....I would like that promote Aug or Sep or Oct as type any...I tried to add OR, but didn't work "Aug" or "Sep" or "Oct", type any.

Thanks

[PC2790]

Hello @Nun ,

Can you please elaborat your requirement? It would be good if you can support it with some sample data and the end result.

[Nun · Accepted solution]

Sure!

the excel file has a column where the the firs row is empty, the second has month (in the month cell there is a formula, if =month(today()) = 8, "Aug",if month(today())=9, "Sep","Oct")) so

colum1

empty

Aug (because the month is 8, but next month the value change to "Sept")

10

20

30

in query M, I removed the first row, and promote the 2 row as header:Table.TransformColumnTypes(#"Promoted Headers",{, {"Aug", type any}.....I would like that promote Aug or Sep or Oct as type any...I tried to add OR, but didn't work "Aug" or "Sep" or "Oct", type any.

Thanks

[PC2790]

Ok, So are looking a way out to avoid hardcoding "Aug" in the step of Promoting headers.

See if this works:

= Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])

[Nun]

this is a step present before the step where I would like there to be a dynamic change based on the current month. The step to be "dynamic", is = Table.TransformColumnTypes(#"Promoted Headers",{{"Aug", type any}}) but "Aug" is present now in the source. Next month is Sep and then Oct. What I would need is that Table.TransformColumnTypes(#"Promoted Headers",{{"Aug" or "Sep" or "Oct", type any}}) but it doesn't work

Thanks

[PC2790]

yes that's why I am advising to remove the dependency on the monthname and simply use:

= Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])

instead of:

= Table.TransformColumnTypes(#"Promoted Headers",{{"Aug", type any}}) 

If you can share your sample data, I can implement it for you.

[Nun · Accepted solution]

yes, you are right, I kept the previous step as = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]) and removed the step with error.  So it works. I don't really need that step. 

Thanks a lot!