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
Chipsahoy1
Frequent Visitor

Table with Blank Rows

Hi Everyone,

 

Thank you in advance for your help.  I'm farily new to Powerbi and had a question.  

 

I have a table where I need to have the blank rows be filled in with the main service listed on the PO.  

 

In the example below, PO 1 has a blank row, but I need that row to show as "hip" for modality, "total" for subtype, and "advanced hip" for the construct.  PO 2's blank row should read knee, partial, and unicondylar.  PO 3's blank rows should read shoulder, total, and reverse shoulder.

 

Is there a DAX that can fill in the blank rows based off of the PO #?

   

POModalitySubtypeConstruct
1HipRevisionRevision
1HipTotalAdvanced Hip
1   
2KneePartialUnicondylar Knee
2KneeOtherUpcharge
2   
3ShoulderTotalReverse Shoulder
3   
3   
8 REPLIES 8
Chipsahoy1
Frequent Visitor

@VahidDM @Samarth_18 @JANHAVIPANDIT28 .  The end goal is to have a separate table where only the main components are listed (see below) based on PO # and not have any of the secondary components listed.

PO #ModalitySubtypeConstruct
1HipTotalAdvanced Hip
2KneePartialUnicondylar Knee
3Shoulder TotalReverse Shoulder
4KneeRevisionRevision

 

Samarth_18
Community Champion
Community Champion

Hi @Chipsahoy1 ,

 

You can follow steps to get your desired output:-

1. Go to trasform data tab, select your Modality,subtype and construct column and right click on it then select replace value option shown below:-

image.png

2. Now put one space in "value to find" and add null in "Replace with"

image.png

3. Now on the top ribbon click on "Fill" and select "Down"

image.png

Below would be the final output and now click on close and apply:-

Samarth_18_0-1632889439356.png

 

Thank You,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

JANHAVIPANDIT28
Frequent Visitor

This can be achived in power query If you are ok with that. Or you want to do it with dax only?

@JANHAVIPANDIT28 I'm open to any possible solutions.  Can you show me what you mean?

Hi @Chipsahoy1 

 

Can you tell us the logic behind the selected items for the blank rows? and can you share a sample result or how you want to use those data?

 

Appreciate your Kudos!!

Hi @VahidDM .  Thanks for your quick response.  The result I would like to get is below.  The reasoning is because these are the major/main items for that PO.  So for PO 1, the main item is listed on PO line #2.  For PO #2, the major item is on line 1 and for PO #3, the major item is on line 1.  

 

The major items aren't always on PO line #s 1, 2, or 3.  They could be on line #10, or 5.  It all depends on who entered the info.  So unfortunately the major item's PO line # is random.

POPO Line#ModalitySubtypeConstruct

1

1HipRevisionRevision
12HipTotalAdvanced Hip
13HipTotalAdvanced Hip
21KneePartialUnicondylar Knee
22KneeOtherUpcharge
23KneePartialUnicondylar Knee
31ShoulderTotalReverse Shoulder
32Shoulder TotalReverse Shoulder
33ShoulderTotalReverse Shoulder


I'm open to suggestions on how this can be achieved. Would creating a separate table to only show the major components be an option (like below table)?

PO Modality SubtypeConstruct
1HipTotal

Advanced Hip

2KneePartial

Unicondylar 

3ShoulderTotal

Reverse Shoulder 

Hi @Chipsahoy1 

 

How can we find which PO line is the main? do you have any table like key table to find that?

 

Appreciate your Kudos!!

 

 

 

@VahidDM Unfortunately not, but there are keywords that I know are not the main components.  for subtype and construct.  The keywords like "revision" and "other" for subtype and "revision", and "upcharge" for construct.  What makes this a bit trickier is, a revision can be a main component if there no other components are listed on that PO (see below).  

PO #PO Line #ModalitySubtypeConstruct

4

1   
42KneeRevisionRevision

 

So for the example above, I would need it to eventually show as this.

 

PO #PO Line #ModalitySubtypeConstruct

4

1KneeRevisionRevision
42KneeRevisionRevision

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

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.

Top Solution Authors