cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
manjaeger Occasional Visitor
Occasional Visitor

Profit Mining / Data from two tables

Hey there!

I already tried to find a solution for my problem in the documentation or the community, but I could not find an answer yet. Would be nice if somebody could help me out!

My issue:

I want to do a profit mining dashboard for car sales in a certain period of time.
Table 1 contains many data records of car sales. Each row represents one car and shows details like color, engine and all the additional equipment. Each 3-digit-code represents one item.
Bildschirmfoto 2019-10-17 um 20.46.17.png

 

As the equipment is represented through a code, I had to create a second table with translations for the 3-digit-codes.

Thus, Table 2 contains the "translations":

Bildschirmfoto 2019-10-17 um 20.51.30.png

 

 

My goal is to connect [Table 1-optional equipment] with [Table 2-Code].

I want to create charts which show the most commonly sold items in general or e.g. the number of items belonging to the category "Interior" sold in a certain period of time.

 

I already tried to do a "many to one" connection and tried out some COUNT funtions but I got stuck every time.

I guess I'm still in the learning process and would be glad if somebody helped me out 🙂

Thanks in advance!
Manuel

2 ACCEPTED SOLUTIONS

Accepted Solutions
sturlaws New Contributor
New Contributor

Re: Profit Mining / Data from two tables

Hi @manjaeger,

I have two strategies you can test out.

 

1. The first one involves splitting the optional equipment codes of Table1 by delimeter in power query(edit queries), then unpivot the resulting columns. If a car is sold with 5 optional equipment types, that car nr will be repeated 5 times. When counting cars, this can be resolved by using distinctcount. The benefit of this strategy is that you can have 1-to-many relationship between table2 and table1. This is especially helpfull if your model becomes complex with many tables and relationships.
example 1

 

2. The second strategy also involves power query. But now create a duplicate of table1, and remove all columns except the optional equipment-column. Then create a duplicate of the remaining column, and split that column by delimter. Then unpivot the resulting columns. The resulting table can be connected to table1 with a many-to-many relationship. And a bidirectional many-to-one relationship to table2. The benefit of this strategy is that it is really easy to find the most popular combinations of optional equipment. The downside is that this works fine in a simple modell like this, but it may give you very unexpected results if the model becomes complex.

example 2

 

Cheers,
Sturla




View solution in original post

Super User
Super User

Re: Profit Mining / Data from two tables

Hi,

In the Query Editor right click on the Optiona Equipment in Table1 and go to Split Column > By delimiter > Space > Advanced > Split by rows > OK.  Now build a relationship from the Optional Equipment column of Table1 to the Code column of Table2.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
sturlaws New Contributor
New Contributor

Re: Profit Mining / Data from two tables

Hi @manjaeger,

I have two strategies you can test out.

 

1. The first one involves splitting the optional equipment codes of Table1 by delimeter in power query(edit queries), then unpivot the resulting columns. If a car is sold with 5 optional equipment types, that car nr will be repeated 5 times. When counting cars, this can be resolved by using distinctcount. The benefit of this strategy is that you can have 1-to-many relationship between table2 and table1. This is especially helpfull if your model becomes complex with many tables and relationships.
example 1

 

2. The second strategy also involves power query. But now create a duplicate of table1, and remove all columns except the optional equipment-column. Then create a duplicate of the remaining column, and split that column by delimter. Then unpivot the resulting columns. The resulting table can be connected to table1 with a many-to-many relationship. And a bidirectional many-to-one relationship to table2. The benefit of this strategy is that it is really easy to find the most popular combinations of optional equipment. The downside is that this works fine in a simple modell like this, but it may give you very unexpected results if the model becomes complex.

example 2

 

Cheers,
Sturla




View solution in original post

Super User
Super User

Re: Profit Mining / Data from two tables

Hi,

In the Query Editor right click on the Optiona Equipment in Table1 and go to Split Column > By delimiter > Space > Advanced > Split by rows > OK.  Now build a relationship from the Optional Equipment column of Table1 to the Code column of Table2.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 4,786 guests
Please welcome our newest community members: