Use Column Name as Filter

[Bhoobala_P]

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

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

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. I will get Attribute column which contains unique value of dates:

Attribute

01-01-2011

01-02-2011

01-03-2011

01-04-2011

However, 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?

P.S. Is there any other way to sort out this issue??? Pls help!

[Sean]

@Bhoobala_P  No need to Duplicate the table

In the Query Editor

1) Transform tab => Use First Row as Headers (if necessary)

2) select Customer ID, onsite/offshore and Region Columns => Unpivot Other Columns
3) Rename New Columns (if necessary) Attribute to Date

4) Close and Apply

Here's the result...

And...

Hope this helps.

Good Luck!

[Anonymous]

Hello,

because of this solution data size will increase let's say we have 1 billion records if we unpivot it then it will convert in 4 Billion. This is not feasible. Actually, I am also facing this issue. Can you suggest me a better solution?

Thanks in advance

[Bhoobala_P]

Dear Sean,

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).

Is there an option to make the date appear as column name as such, when I select 'Use First Row as Header'??

[v-yuezhe-msft]

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]

Dear Lydia,

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

Practice	Employee	Activitity	01-04-2011	01-05-2011	01-06-2011
Project1	Employee1	Travel	50000	50000	50000
Project1	Employee2	Travel	50010	50010	50010
Project1	Employee3	Travel	50020	50020	50020
Project1	Employee4	Travel	50030	50030	50030

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

[MarcelBeug]

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]

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]

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?

[v-yuezhe-msft]

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]

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