cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mellyVK Post Patron
Post Patron

advice on data

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.

 

 sale.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
GarethWoodhouse Resolver I
Resolver I

Re: 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 personJan-19Feb-19Mar-19Apr-19
Peter2345
Alan1245
John5788

To this

DatePeterAlanJohn
Jan-19215
Feb-19327
Mar-19448
Apr-19558

 

You can do this with special paste in excel and select transpose.

 

Better still you would have

SalespersonDateValue
PeterJan-192
PeterFeb-193

 

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.

example2.PNGexample1.PNG

 

Hope this helps.

If so please click the botton to accept this as an answer.

Thanks.

 

 

View solution in original post

6 REPLIES 6
Highlighted
Maxim297
Frequent Visitor

Re: advice on data

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

GarethWoodhouse Resolver I
Resolver I

Re: advice on data

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.

mellyVK Post Patron
Post Patron

Re: advice on data

@Maxim297 @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.

Maxim297
Frequent Visitor

Re: advice on data

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

:Capture.PNGCapture1.PNG

Microsoft v-lionel-msft
Microsoft

Re: advice on data

Hi @mellyVK ,

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

Table

gg3.PNG

Matrix:

gg4.PNG

 

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

Re: 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 personJan-19Feb-19Mar-19Apr-19
Peter2345
Alan1245
John5788

To this

DatePeterAlanJohn
Jan-19215
Feb-19327
Mar-19448
Apr-19558

 

You can do this with special paste in excel and select transpose.

 

Better still you would have

SalespersonDateValue
PeterJan-192
PeterFeb-193

 

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.

example2.PNGexample1.PNG

 

Hope this helps.

If so please click the botton to accept this as an answer.

Thanks.

 

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors