Forum Discussion
Help with promoted headers issue in Query M
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
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!
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
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])
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
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.
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!