Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

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.

Udklip.PNG

 

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 

1 ACCEPTED SOLUTION
vivran22
Community Champion
Community Champion

Hi @Anonymous 

 

I have done it other way around.

 

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

 

1.png

 

 

Then, I removed duplicates post filtering out nulls

 

2.JPG

 

 

Then loaded the tables in Power BI and created relationship:

 

3.png

 

 

Used DAX to include the blank values

 

4.JPG

 

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/

View solution in original post

3 REPLIES 3
Razwan
Helper I
Helper I

@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
Community Champion
Community Champion

Hi @Anonymous 

 

I have done it other way around.

 

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

 

1.png

 

 

Then, I removed duplicates post filtering out nulls

 

2.JPG

 

 

Then loaded the tables in Power BI and created relationship:

 

3.png

 

 

Used DAX to include the blank values

 

4.JPG

 

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
Not applicable

Hi Vivran22 

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

It worked like a charm! 

Kind regards 

Junior-BI-Assi

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.