replacing blank values with the value of a identical row that has a value

[Anonymous]

Hi 

I need to either fill the blank values in the collumn "module ID" with the same number as the one that are filled or create a new collumn as shown in the picture.

In the below picture is is quite clear that the module name "Getoveralltopselling" has module ID 65, but because of bad data collection some of the rows with that module has not noted the module ID (This module is not the only case - There are more than 100 rows which has a module name but no ID, but some other row has that same module name but with an ID) 

How do I tell Power BI to return the value the correct value in the module ID row (In this example 65)? 
I have tried lookupvalue, but i cannot return 65 because the module name and the number 65 appears multiple times.

Afterwards I have another question. 

I need to create a table with a collumn of all distinct values of the modules and another collumn with the specific modules ID. 
Such that the first row would be 
Module name - Module ID

Getoveralltopselling - 65 
module 2 - 41 
Module 3 - 32 

[Razwan]

@Anonymous 
Hi, you can accomplish this easily.
You can start by creating the calculated table with distinct values and based on that, lookup the unique id for each module to fill in the gaps in your main table.

Code for calculated table:

Table = 
    FILTER(
        SUMMARIZE( MyTable,
                MyTable[Module name], 
                MyTable[Module ID]
        ),
    NOT ISBLANK( MyTable[Module ID] )
    )

Code for ModuleID column:

Module ID Fix = LOOKUPVALUE('Table'[Module ID], 'Table'[Module name], MyTable[Module name])

Kind regards,
Razwan

[vivran22 · Accepted solution]

Hi @Anonymous 

I have done it other way around.

First, I created a reference table from the main table in Power Query:

Then, I removed duplicates post filtering out nulls

Then loaded the tables in Power BI and created relationship:

Used DAX to include the blank values

You may find the the sample pbix file here

Hope this solves the purpose!

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

[Anonymous]

Hi Vivran22 

Sorry for the late reply, but thank you very much! 

It worked like a charm! 

Kind regards 

Junior-BI-Assi