Create new table from Master Data without duplicates

[Anonymous]

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 reference	Date	Start Time	Finish Time
QAS1234	2015/01/01	18:33	20:20
QAS1234	2015/01/02	18:54	20:00
QAS1234	2015/01/03	18:45	20:00
QAS1000	2015/01/04	12:50	13:00
QAS4888	2015/01/05	10:45	11:55
QAS3567	2015/01/06	06:30	07:00
QAS3567	2015/01/07	07:00	07:05
QAS001	2015/01/08	13:00	14:00

I need it to look like this:

Load reference	Date	Start Time	Finish Time
QAS1234	2015/01/01	18:33	20:20
QAS1000	2015/01/04	12:50	13:00
QAS4888	2015/01/05	10:45	11:55
QAS3567	2015/01/06	06:30	07:00
QAS001	2015/01/08	13:00	14:00

Thank you

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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