Forum Discussion
advice on data
I have created a working example pbx file for you showing step by step how you can take youe excel file and make it work in PowerBI.
Datawise at a minimum I suggest you transpose your data from this
Sale person Jan-19 Feb-19 Mar-19 Apr-19 Peter 2 3 4 5 Alan 1 2 4 5 John 5 7 8 8 To this
Date Peter Alan John Jan-19 2 1 5 Feb-19 3 2 7 Mar-19 4 4 8 Apr-19 5 5 8 You can do this with special paste in excel and select transpose.
Better still you would have
Salesperson Date Value Peter Jan-19 2 Peter Feb-19 3 Once you have your data better set up the powerbi side is easier.
In the example pbx file I also created a seperate date table by merging the salesperson and location tables together, removing everything except the dat and then deleting duplicates.
This allows you to add a date filter that will show all dates that appear in either the sales person or location tables.
Even better would be to create a date dimension but given you are just starting out what I have described above is easier for now.
If you want the example pbx file then just private message me your email address and I will send it over.
Hope this helps.
If so please click the botton to accept this as an answer.
Thanks.
I have created a working example pbx file for you showing step by step how you can take youe excel file and make it work in PowerBI.
Datawise at a minimum I suggest you transpose your data from this
| Sale person | Jan-19 | Feb-19 | Mar-19 | Apr-19 |
| Peter | 2 | 3 | 4 | 5 |
| Alan | 1 | 2 | 4 | 5 |
| John | 5 | 7 | 8 | 8 |
To this
| Date | Peter | Alan | John |
| Jan-19 | 2 | 1 | 5 |
| Feb-19 | 3 | 2 | 7 |
| Mar-19 | 4 | 4 | 8 |
| Apr-19 | 5 | 5 | 8 |
You can do this with special paste in excel and select transpose.
Better still you would have
| Salesperson | Date | Value |
| Peter | Jan-19 | 2 |
| Peter | Feb-19 | 3 |
Once you have your data better set up the powerbi side is easier.
In the example pbx file I also created a seperate date table by merging the salesperson and location tables together, removing everything except the dat and then deleting duplicates.
This allows you to add a date filter that will show all dates that appear in either the sales person or location tables.
Even better would be to create a date dimension but given you are just starting out what I have described above is easier for now.
If you want the example pbx file then just private message me your email address and I will send it over.
Hope this helps.
If so please click the botton to accept this as an answer.
Thanks.