Forum Discussion

Nun's avatar
Nun
Resolver I
4 years ago
Solved

Help with promoted headers issue in Query M

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 Septemb...
Need Help
  • Nun's avatar
    Nun
    4 years ago

    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

  • Nun's avatar
    Nun
    4 years ago

    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!

PC2790's avatar
PC2790
Community Champion
4 years ago

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's avatar
    Nun
    Resolver I
    4 years ago

    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's avatar
      PC2790
      Community Champion
      4 years ago

      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's avatar
        Nun
        Resolver I
        4 years ago

        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