cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Need 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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

12 REPLIES 12
Highlighted
Super User I
Super User I

Re: Need help on how to set up database

Hi there,

 

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

 

Regards,

 

robbe

Highlighted
Post Patron
Post Patron

Re: Need help on how to set up database

Thanks so much. I am open to any ideas.

 

customercodeEntry Date Timenurseseqformcdformseqans
2587701Proc18/9/2019 9:07EMP:21799218KL9871Purchased supplies
2587701stMD18/9/2019 9:07EMP:21799218KL9871Smith, John
2587701stST18/9/2019 9:07EMP:21799218KL9871Doe, Jane
258770Date18/9/2019 9:07EMP:21799219KL9871080919
258770Drop18/9/2019 9:56EMP:21799219KL98710954
258770InRm18/9/2019 9:07EMP:21799219KL98710840
258770Out18/9/2019 9:56EMP:21799219KL98710954
258770Pause18/9/2019 9:07EMP:21799219KL98710901
258770Stop18/9/2019 9:56EMP:21799219KL987101952
258770Stop18/13/2019 13:10EMP:21799220KL98710954
2587701Proc18/10/2019 16:00EMP:21950278KL9872Purchased supplies
2587701stMD18/10/2019 16:00EMP:21950278KL9872Smith, John
2587701stST18/10/2019 16:00EMP:21950278KL9872Doe, Jane
258770Date18/10/2019 16:00EMP:21950278KL9872081019
258770Drop18/10/2019 16:00EMP:21950278KL98721610
258770InRm18/10/2019 16:00EMP:21950278KL98721600
258770Out18/10/2019 16:00EMP:21950278KL98721645
258770Pause18/10/2019 16:00EMP:21950278KL98721602
258770Stop18/10/2019 16:00EMP:21950278KL98721725
Highlighted
Super User I
Super User I

Re: Need help on how to set up database

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! 🙂 

Highlighted
Post Patron
Post Patron

Re: Need help on how to set up database

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.

Highlighted
Super User III
Super User III

Re: Need 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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Need help on how to set up database

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!

Highlighted
Super User III
Super User III

Re: Need help on how to set up database

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

 

Not sure about your remark about the date and time: Do you need a new code here?

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Post Patron
Post Patron

Re: Need help on how to set up database

Thank you for the video. I will watch it right away.

 

As for the date and time - not sure if it matters but in the database, there are TWO Columns.  nsma1_dt and nsma1_tm so the date and time are separated - not a combined field. 

Highlighted
Post Patron
Post Patron

Re: Need help on how to set up database

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

 

screenshot.jpg

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors