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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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?

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.