cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dnExplorer71
Regular Visitor

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  @dnExplorer71 ,

 

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

@dnExplorer71 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.






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.





dnExplorer71
Regular Visitor

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  @dnExplorer71 ,

 

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

@dnExplorer71 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.






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

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






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.





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

@dnExplorer71 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!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

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 June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!