cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Qhawe Frequent Visitor
Frequent Visitor

Create new table from Master Data without duplicates

Hi Everyone.

 

I am new to PowerBI and appreciate your help.

I am in the transport industry and I have a master data sheet which shows every single load that was done and every drop on each load. So in the data sheet, load numbers can be duplicated depending on how many drops exist on that load.

 

I need to create a seperate table from the master that I can use to analyse at trip leave (ie per load).

To do this I need a column in the new table with all the unique load numbers (without duplicates) and I need the rest of the columns to reference the load number and provide the assocaited value.

 

I tried using the "lookupvalue" function but it doesnt work when there are multiple values linked to the reference (load number).

So ideally in that situation I need to column to show the first value associated with that load number.

Below is an example of some of the columns:

 

Load referenceDateStart TimeFinish Time
QAS12342015/01/0118:3320:20
QAS12342015/01/0218:5420:00
QAS12342015/01/0318:4520:00
QAS10002015/01/0412:5013:00
QAS48882015/01/0510:4511:55
QAS35672015/01/0606:3007:00
QAS35672015/01/0707:0007:05
QAS0012015/01/0813:0014:00

 

I need it to look like this:

 

Load referenceDateStart TimeFinish Time
QAS12342015/01/0118:3320:20
QAS10002015/01/0412:5013:00
QAS48882015/01/0510:4511:55
QAS35672015/01/0606:3007:00
QAS0012015/01/0813:0014:00

 

Thank you

3 REPLIES 3
Pauwnrajpp Member
Member

Re: Create new table from Master Data without duplicates

Hi Qhawe,

 

You can have another table with the different name and it has the distinct values of load numbers with the related info.

 

You can do transformations in Power BI to remove the columns that are not required.

 

Regards,

Pradeep

Qhawe Frequent Visitor
Frequent Visitor

Re: Create new table from Master Data without duplicates

Hi Pauwnrajpp.

 

Thank you for your response.

Once I have to distinct values on load reference in the new table, how do I get start time and finish time to give me disctinct value linked to the load reference number?

 

My issue has been that the lookupvalue function give an error that "multiple values were found".

 

Regards

Qhawe

Pauwnrajpp Member
Member

Re: Create new table from Master Data without duplicates

You can get those start time and finish time for the distinct values from table 1.

 

Because, in your Data Model, Table 1 and Table 2 has duplicate entries to connect with the relation.

 

To avoid this, get the distinct value from the table 2 key column and have it inbetween table 1 and table 2.

 

This new table is called factless fact table and it should contain only distinct key value.

 

1. Table 1 to factlessfact table will have many to one relationship

2. Factlessfact table to Table 2 will have one to many relationship and it should be bidirectional.

 

Regards,

Pradeep