Forum Discussion
Post PatronNeed help on how to set up database
I am struggling with how to organize the data from the database I can connect to and wondering if someone has any suggestions. I have attached some sample data
The database has 8 columns with answers to form questions. Each answer is it's own line. I need to pivot the table so it is one line with the answers all one one line based on the formcode-seq. The issue I am running into is if a mistake is made on an answer and the person goes back and fixes it, there are two lines with the same answer code and I only need the LAST answer. In the sample data - the code Stop1 has two answers for the same form-seq.
Then the date should be the date from the first entry on that form.
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"
12 Replies
- RobbeVL
Impactful Individual
Hi there,
Could you please copy your data in here so I can model it for you ?
Regards,
robbe
kattleesThanks so much. I am open to any ideas.
customer code Entry Date Time nurse seq formcd formseq ans 258770 1Proc1 8/9/2019 9:07 EMP:21799 218 KL987 1 Purchased supplies 258770 1stMD1 8/9/2019 9:07 EMP:21799 218 KL987 1 Smith, John 258770 1stST1 8/9/2019 9:07 EMP:21799 218 KL987 1 Doe, Jane 258770 Date1 8/9/2019 9:07 EMP:21799 219 KL987 1 080919 258770 Drop1 8/9/2019 9:56 EMP:21799 219 KL987 1 0954 258770 InRm1 8/9/2019 9:07 EMP:21799 219 KL987 1 0840 258770 Out1 8/9/2019 9:56 EMP:21799 219 KL987 1 0954 258770 Pause1 8/9/2019 9:07 EMP:21799 219 KL987 1 0901 258770 Stop1 8/9/2019 9:56 EMP:21799 219 KL987 1 01952 258770 Stop1 8/13/2019 13:10 EMP:21799 220 KL987 1 0954 258770 1Proc1 8/10/2019 16:00 EMP:21950 278 KL987 2 Purchased supplies 258770 1stMD1 8/10/2019 16:00 EMP:21950 278 KL987 2 Smith, John 258770 1stST1 8/10/2019 16:00 EMP:21950 278 KL987 2 Doe, Jane 258770 Date1 8/10/2019 16:00 EMP:21950 278 KL987 2 081019 258770 Drop1 8/10/2019 16:00 EMP:21950 278 KL987 2 1610 258770 InRm1 8/10/2019 16:00 EMP:21950 278 KL987 2 1600 258770 Out1 8/10/2019 16:00 EMP:21950 278 KL987 2 1645 258770 Pause1 8/10/2019 16:00 EMP:21950 278 KL987 2 1602 258770 Stop1 8/10/2019 16:00 EMP:21950 278 KL987 2 1725
