Forum Discussion

Post Patron

7 years ago

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

Need Help

Tutorial Requests

ImkeF

7 years ago

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"

RobbeVL

Impactful Individual

7 years ago

Hi there,

Could you please copy your data in here so I can model it for you ?

Regards,

robbe

kattlees

Post Patron

7 years ago

Thanks 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

RobbeVL

Impactful Individual

7 years ago

1 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! :)

kattlees

Post Patron

7 years ago

A 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

7 years ago

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"

Forum Discussion

Need help on how to set up database

Related content

Database connectivity.

Mirrored Snowflake Database Storage

Fabric Mirrored Databases

Mirrored Databases Auditing

Moving Mirrored SQL database between workspace