Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 #?
PO | Modality | Subtype | Construct |
1 | Hip | Revision | Revision |
1 | Hip | Total | Advanced Hip |
1 | |||
2 | Knee | Partial | Unicondylar Knee |
2 | Knee | Other | Upcharge |
2 | |||
3 | Shoulder | Total | Reverse Shoulder |
3 | |||
3 |
@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 # | Modality | Subtype | Construct |
1 | Hip | Total | Advanced Hip |
2 | Knee | Partial | Unicondylar Knee |
3 | Shoulder | Total | Reverse Shoulder |
4 | Knee | Revision | Revision |
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:-
2. Now put one space in "value to find" and add null in "Replace with"
3. Now on the top ribbon click on "Fill" and select "Down"
Below would be the final output and now click on close and apply:-
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
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.
PO | PO Line# | Modality | Subtype | Construct |
1 | 1 | Hip | Revision | Revision |
1 | 2 | Hip | Total | Advanced Hip |
1 | 3 | Hip | Total | Advanced Hip |
2 | 1 | Knee | Partial | Unicondylar Knee |
2 | 2 | Knee | Other | Upcharge |
2 | 3 | Knee | Partial | Unicondylar Knee |
3 | 1 | Shoulder | Total | Reverse Shoulder |
3 | 2 | Shoulder | Total | Reverse Shoulder |
3 | 3 | Shoulder | Total | Reverse 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 | Subtype | Construct |
1 | Hip | Total | Advanced Hip |
2 | Knee | Partial | Unicondylar |
3 | Shoulder | Total | 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 # | Modality | Subtype | Construct |
4 | 1 | |||
4 | 2 | Knee | Revision | Revision |
So for the example above, I would need it to eventually show as this.
PO # | PO Line # | Modality | Subtype | Construct |
4 | 1 | Knee | Revision | Revision |
4 | 2 | Knee | Revision | Revision |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
22 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
34 | |
25 | |
23 | |
23 |