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.
Anonymous Anonymous
It's not a matrix, so i was wondering if it can be done, so better to spilt them into sheets
They are 2 separate table and not related. The only common field between the tables is the date.
Can anyone advice....Thank you.
As Garetwoodhouse mention,
You can make a seperate table that you can link, this you can do manually or with merge queries (in edit queries) last option will update automatically if you have new shop location and sale persons, let me know if this works or you need more info,
Kr, Maxim
: