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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.