Use Column Name as Filter

Question

Guys,&nbsp;I have a dataset like this:Customer IDonsite/offshoreRegion01-01-201101-02-201101-03-201101-04-2011Customer1onsite&nbsp;&nbsp;&nbsp;Kuwait200000200000200000300000Customer2offshoreUK200000200000200000300000Customer3onsite&nbsp;&nbsp;&nbsp;USA210000210000210000210000Customer4offshoreUSA220000220000220000220000Customer5offshoreUSA210000210000210000230000Customer6offshoreDenmark220000220000220000240000Customer7onsite&nbsp;&nbsp;&nbsp;Australia230000230000230000250000&nbsp;I want to create a chart for the data, with time filter (viz., the column name).Problems I face are:1. When I load this data into Power BI, it will not load date as a column name!! Instead it gives default column names (Column4, Column5, Column6, etc). How to load date as column name??&nbsp;2. Assuming I can upload data with dates as column names, I make a duplicate of the table. If I use unpivot data by selecting the date columns alone (i.e., column 4 to 7) from the duplicate.&nbsp;I will get Attribute column which contains&nbsp;unique value of dates:Attribute01-01-201101-02-201101-03-201101-04-2011However, if I try to use this attribute column as a filter, it doesn't work!! How can I establish relationship between data in the original table and the attribute column of duplicate table?&nbsp;P.S. Is there any other way to sort out this issue??? Pls help!

sean · Answer

Bhoobala_P&nbsp; No need to Duplicate the tableIn the Query Editor1) Transform tab =&gt; Use First Row as Headers (if necessary)2) select Customer ID, onsite/offshore and Region Columns =&gt; Unpivot Other Columns3) Rename New Columns (if necessary) Attribute to Date4) Close and Apply&nbsp;Here's the result...&nbsp;&nbsp;And...&nbsp;&nbsp;Hope this helps.Good Luck! :smileyhappy:

bhoobala_p · Answer

Dear Sean,&nbsp;Thanks, that really helped me. However in the first step, i.e., when I load the data and selected 'Use First Row as Header', it still converts the date value into default text(Column5, Column6, etc).&nbsp;Is there an option to make the date appear as column name as such, when I select 'Use First Row as Header'??

anonymous · Answer

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).
&nbsp;
Thanks,Lydia Zhang

bhoobala_p · Answer

Dear Lydia,&nbsp;Thanks for the reply. Let me give some clarity to the picture. This is the sample data:PracticeEmployeeActivitity01-04-201101-05-201101-06-2011Project1Employee1Travel500005000050000Project1Employee2Travel500105001050010Project1Employee3Travel500205002050020Project1Employee4Travel500305003050030&nbsp;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 · Answer

Hi Bhoobala_P，What is the data type of Column4, Column5, Column6 in your scecond screenshot?In your&nbsp;third screenshot, delete the last two steps from "APPLIED STEPS"&nbsp;, then choose first row as header and check the result.Thanks,Lydia Zhang

Forum Discussion

Use Column Name as Filter

10 Replies

Related content

Slicer for filtering several columns

Apply data to all rows in a filtered column

Adding filters to a calculated column

Filter

odata Report Level Filters Multiple Filters Same Column

Customer ID	onsite/offshore	Region	01-01-2011	01-02-2011	01-03-2011	01-04-2011
Customer1	onsite	Kuwait	200000	200000	200000	300000
Customer2	offshore	UK	200000	200000	200000	300000
Customer3	onsite	USA	210000	210000	210000	210000
Customer4	offshore	USA	220000	220000	220000	220000
Customer5	offshore	USA	210000	210000	210000	230000
Customer6	offshore	Denmark	220000	220000	220000	240000
Customer7	onsite	Australia	230000	230000	230000	250000