Profit Mining / Data from two tables

[Anonymous]

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.

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":

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

[sturlaws · Accepted solution]

Hi @Anonymous,

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

[Ashish_Mathur · Accepted solution]

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.

[sturlaws · Accepted solution]

Hi @Anonymous,

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