advice on data

[Anonymous]

hi all

i have this data in a single excel sheet that contain both the sale of the sale person and shop location.

Is it possible to use this current format in power bi, if yes, how should i go about it?
Any advice is grateful.

 

[GarethWoodhouse · Accepted solution]

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.

[v-lionel-msft]

Hi @Anonymous ,

As tested, without changing the original data, you can get the visual as below in Power BI:

Table：

Matrix:

Even if the data is split into two tables, the two cannot establish a relationship.

So it is better to split the data into two tables and display them in two visuals.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GarethWoodhouse]

If that isn't a matrix then you may want to consider breaking it across sheets in excel and loading seperately.

Sheet1 for Sales Person,

Sheet 2 for Shop and then pull each sheet in as a seperate table in PowerBI.

If you have another sheet that shows what sales person is in each shop then you could link that aswell..

Hope that helped.

[Anonymous]

Hello,

Yes you can use the matrix visual. Where you take the date in column and sales person in the rows.

If you send me the excel I can give you an example.

Kind regards

Max

[Anonymous]

@Anonymous @GarethWoodhouse 

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.

[Anonymous]

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

: