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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Replace value if ID condition is met

I have a Power BI desktop which currently connects to a CSV file.  This file is for a report that needs to be updated monthly or adhoc as required.  The file contains hundreds of records which is formatted as below.  Netdays is a calcuation within Power Bi. 

 

IDCreated DateResolved DateNetDays
ID12342021-01-012021-07-23203
ID12352021-02-052021-02-072

 

The problem I have encountered is that about 30 individual records has an incorrect Resolved Date.   My initial solution was to update the dates for those records in the system itself, but the system administrator said they cannot make the update and tried  everything they could. So the only option I have is to replace the date for those records manually and will have to be done routinely.  

 

I have merge another excel file and appended it to the end of the current table with the 30 records and the correct dates.

IDCreated DateResolved DateNetDaysMerged Date
ID12342021-01-012021-07-232032021-01-05
ID12352021-02-052021-02-072null

 

Replacing the Resolve Date directly would be optimal for me  because my NetDays column is currently calculation the difference between the Resolved Date and Created Date which also removes weekends.

How do I then have Power BI take check the Merged Date values if a value exists to replace the one in the Resolved Date?

 

1 ACCEPTED SOLUTION

Hi  @Anonymous ,

 

Using below M codes,you will easily replace the values in column "Resolved Date" with values in "Merged Date" if they are not null:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxNDI2UdJRMtQ31DcyMDIEMs31jYxhbCMDY7CkKUQgVgeqxRQkBxMFMc3hTCDOK83JUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Created Date" = _t, #"Resolved Date" = _t, NetDays = _t, #"Merged Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Created Date", type date}, {"Resolved Date", type date}, {"NetDays", Int64.Type}, {"Merged Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Merged Date]=null then [Resolved Date] else [Merged Date]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [Resolved Date],each [Custom],Replacer.ReplaceValue,{"Resolved Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Custom"})
in
    #"Removed Columns"

And you will see:(After replacing)

vkellymsft_0-1629427693530.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

@Anonymous if you add another column as I outlined, you will not be required to make any change, End of the data, PQ is going to return the same column name "Resolved Date" which is already used "Merged Data" data and you will not be required to make any change. Not sure if you followed all 3 steps I outlined in my previous reply. 

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

One of my challenges is that I have already created a dashboard on the existing table.  If one of the solutions I have to add requires me to add another column, I think I have to rework a couple of calculated measures and some visualizations. I'm hoping to not have to do this but maybe it's the only way? 

Hi  @Anonymous ,

 

Using below M codes,you will easily replace the values in column "Resolved Date" with values in "Merged Date" if they are not null:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxNDI2UdJRMtQ31DcyMDIEMs31jYxhbCMDY7CkKUQgVgeqxRQkBxMFMc3hTCDOK83JUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Created Date" = _t, #"Resolved Date" = _t, NetDays = _t, #"Merged Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Created Date", type date}, {"Resolved Date", type date}, {"NetDays", Int64.Type}, {"Merged Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Merged Date]=null then [Resolved Date] else [Merged Date]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [Resolved Date],each [Custom],Replacer.ReplaceValue,{"Resolved Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Custom"})
in
    #"Removed Columns"

And you will see:(After replacing)

vkellymsft_0-1629427693530.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

parry2k
Super User
Super User

@Anonymous another way is to use the following steps:

 

Step 1 -  add another custom column in PQ, let's call it New Date and M code is if [Merged Date] = null then [Resolve Date] else [Merged Date]

Step 2 - remove Merged Date and Resolved Date columns

Step 3 - Rename New Date column to Resolved Date

 

At this point, Resolved Date column is overwritten by Merged Date where Merged Date is available.

 

if Net Days calculation is done in PQ, add these steps before Net Days calculation, if it is done thru DAX, no change is required in the DAX

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@Anonymous are you calculating Net Days in PQ or with DAX?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

The NetDays calcuation is done by invoking DAX code which I have used from another source online.  I hope this helps you understand what has been built so far.

Greg_Deckler
Super User
Super User

@Anonymous I can thing of a couple approaches to this. One would be to add an extra column with a 1 in it for the 30 records. Sort your table, use Table.Buffer and then Remove Duplicates on your ID column. The 1 rows should filter to the top and thus this will remove the duplicate ID rows with the bad resolve date.

 

Another approach would be to use a separate query and table (do not append) and write a function to do the replacement.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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