Hi all,
I need some help please - any help is greatly appreciated.
I have a table called Tickets that includes Project ID as a column. I also have a second table called ProjectPortfolio that is a Direct Query (this can't be changed as I need the live data). The two tables are in the format below:
| Table: Tickets | | | Table 2: ProjectPortfolio |
| | | | | |
| Project ID | | | ViewID | ProjectName |
| 1009 | | | 1009 | Name 1 |
| 1009.1 | | | 1009 | Name 2 |
| unknown | | | 1009.1 | Name 3 |
| 100869 | | | 100869 | Name 4 |
| 100855 | | | null | Name 5 |
| unknown | | | null | Name 5 |
| 100855.2 | | | 100855 | Name 6 |
| | | | 100855.2 | Name 7 |
| | | | null | Name 8 |
My issue is that in the table ProjectPortfolio, there are duplicate values in both the View ID and Project Name columns and because the table is a Direct Query, I can't use the standard Remove Duplicates function in transform data.
I also can't clean the data in the source as this is pulled via an SQL query that I don't have access to change.
Therefore, I need to clean this data in PBI before merging it with Table Tickets. I believe this needs to be done in two stages:
First: remove duplicates populated with a number in the View ID column whilst keeping all null rows e.g:
Note - the view ID 1009 with ProjectName equalling Name 2 has been removed.
| ViewID | ProjectName |
| 1009 | Name 1 |
| 1009.1 | Name 3 |
| 100869 | Name 4 |
| null | Name 5 |
| null | Name 5 |
| 100855 | Name 6 |
| 100855.2 | Name 7 |
| null | Name 8 |
I then need to remove duplicates from the ProjectName column where the ViewID = null. The table will then look like this:
Note - the duplicate row with null ViewID and ProjectName as Name 5 has been removed.
| ViewID | ProjectName |
| 1009 | Name 1 |
| 1009.1 | Name 3 |
| 100869 | Name 4 |
| null | Name 5 |
| 100855 | Name 6 |
| 100855.2 | Name 7 |
| null | Name 8 |
The reason for doing all this is to do a lookup of the Project ID in table Tickets to the ViewID column in the table ProjectPortfolio and return the Project Name as an appended column in the table Tickets. The expected table at the end of all of this is:
Tickets
| Project ID | ProjectName |
| 1009 | Name 1 |
| 1009.1 | Name 3 |
| unknown | null |
| 100869 | Name 4 |
| 100855 | Name 6 |
| unknown | null |
| 100855.2 | Name 7 |
For info, I've already tried:
- Merging the two tables in their current format - this didn't work as the rows in the table Tickets are duplicated because of the duplicate rows in the ProjectPortfolio table
- Creating two duplicates of the table ProjectPortfolio, filtering out null in one version & including null values in the other and then joining them back together once I've cleared the duplicates. This led to an error saying that the function wasn't supported in Direct Query.
On the face of it, this seems like it should be a simple tax but the Direct Query aspect of it makes it very difficult.
I'd be very grateful if someone could advise how I can achieve the desired result, I imagine this is using DAX in transform data but Google searches to this point have been fruitless.
Thanks in advance.
Pete
