Forum Discussion

Bhoobala_P's avatar
Bhoobala_P
Frequent Visitor
9 years ago

Use Column Name as Filter

Guys,   I have a dataset like this: Customer ID onsite/offshore Region 01-01-2011 01-02-2011 01-03-2011 01-04-2011 Customer1 onsite    Kuwait 200000 200000 200000 300000 Custo...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Bhoobala_P,

When you select 'Use First Row as Header', it is impossible to make the date column heading as date type, we are only able to change type for values in one column.

After you Unpivot columns, you are able to change the type of date value in Attribute column to Date type(select the whole Attribute column and right click on it, you will get the following screenshot).

 


Thanks,
Lydia Zhang

Bhoobala_P's avatar
Bhoobala_P
Frequent Visitor
9 years ago

Dear Lydia,

 

Thanks for the reply. Let me give some clarity to the picture. This is the sample data:

PracticeEmployeeActivitity01-04-201101-05-201101-06-2011
Project1Employee1Travel500005000050000
Project1Employee2Travel500105001050010
Project1Employee3Travel500205002050020
Project1Employee4Travel500305003050030

 

Pls refer to the snapshots below:

1. Loading the data2. Choosing first row as header3. Result (issue)

Now, how can we make the date appear as such, after making the first row as header in the query editor???

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Bhoobala_P，

    What is the data type of Column4, Column5, Column6 in your scecond screenshot?

    In your third screenshot, delete the last two steps from "APPLIED STEPS" , then choose first row as header and check the result.

    Thanks,
    Lydia Zhang

    • Bhoobala_P's avatar
      Bhoobala_P
      Frequent Visitor
      9 years ago

      The data type of these three columns is currency, i.e., they are basically revenues in $. 

      And yes Lydia, I tried deleting the two steps and tried to promote the first rows as headers right of the navigation step (default one).

      Still the same issue..!

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    In order to have values, other than text or numbers, promoted to headers, you must use the second argument of Table.PromoteHeaders as in:

     

    = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true])

     

    • Bhoobala_P's avatar
      Bhoobala_P
      Frequent Visitor
      9 years ago

      Dear Marcel,

       

      I did the same after undoing all the other changes except Promote Headers. I added [PromoteAllScalars=true] into the table formula, yet the same problem persists...!

       

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        Can you share another screenshot? With the Table.PoromoteHeader step as the last step, like in your step3 above, but with the adjusted formula? And also the step prior to that step, with the dates in the first row?