Forum Discussion
Solved
Need 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 an...
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"
ImkeF I was super excited for your code and got it to connect to database and pivot as I wanted. But I just noticed it isn't keeping the last entered record. See attached screenshots when I put it to just one record.
When I click on Remove Duplicates - it removed the wrong one. Should always KEEP the one with the higher seq #.
Hi kattlees
Thats because I've sorted by Entry Date Time and not by that number, sorry about that. So you have to adjust the 3rd step like so:
#"Sorted Rows" = Table.Buffer(Table.Sort(ChangeType,{{"nsma1_formsec", Order.Descending}})),