Forum Discussion
Post PatronNeed help on how to set up database
Hi kattlees ,
this code should do if your data is not too large:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdNRa4MwEAfwryI+C96lxuR8dg/tViZzb6UP0gYsrComfv9FNphp52xkL+FIyI/Lkf/hEDIuhYAwCrHo2xPaAmQMFDNACoAyEHbraV9kDAWRrRlKuz6/kBxPxgvF0J/qSqtzoIeu+7goHR6jqazNPl8ll9eLqaNg19bNHVm+ryLzVlmwapQL5pVRD3l040kgu+dafdvdWzxdtIgnrrRt3q7rukrAlV4H8z8tFdWg102KAF2qNCvnhMTZ75SMcfMl4SZDuJUYLD1vmgKEbyrN4IciPtZMTD8W80yBn/xQCvzI5RT4eRLhjxT4WZgizMbAl4LZHPhKCZ8PgndXM9/XnxLMtnX8BA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [customer = _t, code = _t, #"Entry Date Time" = _t, nurse = _t, seq = _t, formcd = _t, formseq = _t, ans = _t]), ChangeType = Table.TransformColumnTypes(Source,{{"customer", Int64.Type}, {"code", type text}, {"Entry Date Time", type text}, {"nurse", type text}, {"seq", Int64.Type}, {"formcd", type text}, {"formseq", Int64.Type}, {"ans", type text}}), #"Sorted Rows" = Table.Buffer(Table.Sort(ChangeType,{{"Entry Date Time", Order.Descending}})), #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"customer", "code", "formcd", "formseq"}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Duplicates", {{"Entry Date Time", type datetime}}, "en-US"), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Entry Date Time", type date}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Entry Date Time", "nurse", "seq"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[code]), "code", "ans"), #"Merged Queries" = Table.NestedJoin(#"Pivoted Column", {"customer", "formcd", "formseq"}, #"Changed Type1", {"customer", "formcd", "formseq"}, "Pivoted Column", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Date", each List.Min([Pivoted Column][Entry Date Time])), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Pivoted Column"}) in #"Removed Columns1"
Impactful Individual1 More thing.
How do you identify a mistake correction? --> 2 times the same value in a row?
What is the nurse field? should be taken into account?
ImkeF Helped me with some advanced M query in the past. If you're lucky she will have a look here! :)
Post PatronA mistake correction is one with the same form, formseq and code but a different time.
If I have multiple entries for the same customer with the same form, form seq and code, i always want the value with the LAST entry date time. Would also be a higher seq #.
The nurse field doesn't have to be taken into account. There can be multiple ones for the same customer/form seq code unless you can put that value next to the entry so we know what nurse did it.
- ImkeF
Community Champion
Hi kattlees ,
this code should do if your data is not too large:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdNRa4MwEAfwryI+C96lxuR8dg/tViZzb6UP0gYsrComfv9FNphp52xkL+FIyI/Lkf/hEDIuhYAwCrHo2xPaAmQMFDNACoAyEHbraV9kDAWRrRlKuz6/kBxPxgvF0J/qSqtzoIeu+7goHR6jqazNPl8ll9eLqaNg19bNHVm+ryLzVlmwapQL5pVRD3l040kgu+dafdvdWzxdtIgnrrRt3q7rukrAlV4H8z8tFdWg102KAF2qNCvnhMTZ75SMcfMl4SZDuJUYLD1vmgKEbyrN4IciPtZMTD8W80yBn/xQCvzI5RT4eRLhjxT4WZgizMbAl4LZHPhKCZ8PgndXM9/XnxLMtnX8BA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [customer = _t, code = _t, #"Entry Date Time" = _t, nurse = _t, seq = _t, formcd = _t, formseq = _t, ans = _t]), ChangeType = Table.TransformColumnTypes(Source,{{"customer", Int64.Type}, {"code", type text}, {"Entry Date Time", type text}, {"nurse", type text}, {"seq", Int64.Type}, {"formcd", type text}, {"formseq", Int64.Type}, {"ans", type text}}), #"Sorted Rows" = Table.Buffer(Table.Sort(ChangeType,{{"Entry Date Time", Order.Descending}})), #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"customer", "code", "formcd", "formseq"}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Duplicates", {{"Entry Date Time", type datetime}}, "en-US"), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Entry Date Time", type date}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Entry Date Time", "nurse", "seq"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[code]), "code", "ans"), #"Merged Queries" = Table.NestedJoin(#"Pivoted Column", {"customer", "formcd", "formseq"}, #"Changed Type1", {"customer", "formcd", "formseq"}, "Pivoted Column", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Date", each List.Min([Pivoted Column][Entry Date Time])), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Pivoted Column"}) in #"Removed Columns1"- kattlees
Wow - where do I begin! I have never done the advanced editor before so I am excited to try this.
one big thing is that the entry date and entry time are actually two columns combined nsma1_dt and nsma1_tm. I had merged them in the database prior to getting the data for you so my apologies there.Since i have never done the editor before which part do I change to pull from my actual database? I'm assuming it's the very first part and I change my source to the actual database?
Thank you so much for your help!
- ImkeF
Hi kattlees ,
once you've pasted the code into the advanced editor, you can leave it and edit the steps from the UI where needed. You have to edit the first step "Source" to connect to your DB. Please check this video - hopefully it helps: http://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-code-into-your-existing/m-p/179314
Not sure about your remark about the date and time: Do you need a new code here?