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 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!

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!

6 Replies

  • 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])