Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
@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!
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
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'??
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
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:
Now, how can we make the date appear as such, after making the first row as header in the query editor???
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])
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...!
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?
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
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..!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |