Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
ID | Created Date | Resolved Date | NetDays |
ID1234 | 2021-01-01 | 2021-07-23 | 203 |
ID1235 | 2021-02-05 | 2021-02-07 | 2 |
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.
ID | Created Date | Resolved Date | NetDays | Merged Date |
ID1234 | 2021-01-01 | 2021-07-23 | 203 | 2021-01-05 |
ID1235 | 2021-02-05 | 2021-02-07 | 2 | null |
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?
Solved! Go to 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)
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@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.
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)
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@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.
@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.
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.
@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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |