cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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


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?

VahidDM
Super User
Super User

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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!