cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
areymejias Frequent Visitor
Frequent Visitor

2 Qualitative information Columns into a X yes/no or count event columns.

Hello everyone,

 

I do have a datashet like this:

 

PatientDiagnoses positionDiagnoses code
Pac0011F20.0
Pac0012F21.0
Pac0021F21.0
Pac0031F20.0
Pac0032F23.0
Pac0033F24.0
Pac0041F21.0

 and i do want to obtain this:

 F20.0F21.0F23.0F24.0
Pac001YesYesNoNo
Pac002NoYesNoNo
Pac003YesNoYesYes
Pac004NoYesNoNo

Or more preferabily:

 F20.0F21.0F23.0F24.0
Pac0011100
Pac0020100
Pac0031011
Pac004010

0

and it would be awesome something like sums up the total o diagnoses, for example if the Pac001 has F20.0 3 times in his life the number in the cell should be 3 better than 1.

 

I have tried bit pivot, unpivot, group by and i did run out of ideas

 

Someone can help me?

 

Thanks in advance,

Angel

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: 2 Qualitative information Columns into a X yes/no or count event columns.

Could you Please try this....

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhMNjAwVNJRAmE3IwM9A6VYHSRhI7CwIbKwEVw1irAxdkOM4YYYowsbg4VNkIVNUM2OBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Patient = _t, #"Diagnoses position" = _t, #"Diagnoses code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Patient", type text}, {"Diagnoses position", Int64.Type}, {"Diagnoses code", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Diagnoses code"]), "Diagnoses code", "Diagnoses position"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"F20.0", "F21.0", "F23.0", "F24.0"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "F20.0 Flag", each if [F20.0]>0 then "Yes" else "No"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "F21.0 Flag", each if [F21.0]>0 then "Yes" else "No"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "F23.0 Flag", each if [F23.0]>0 then "Yes" else "No"),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "F24.0 Flag", each if [F24.0]>0 then "Yes" else "No"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"F20.0", "F21.0", "F23.0", "F24.0"})
in
    #"Removed Columns"

image.pngInputimage.pngOutput



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

Community Support Team
Community Support Team

Re: 2 Qualitative information Columns into a X yes/no or count event columns.

Hi @areymejias,

 

Sample for your reference.

 

Capture.PNG

 

M code in Advanced editor.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhMNjAwVNJRAmE3IwM9A6VYHSRhI7CwIbKwEVw1irAxdkOM4YYYowsbg4VNkIVNUM2OBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Patient = _t, #"Diagnoses position" = _t, #"Diagnoses code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Patient", type text}, {"Diagnoses position", Int64.Type}, {"Diagnoses code", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Patient]), "Patient", "Diagnoses position", List.Count),
    #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Diagnoses code", type text}, {"F20.0", Int64.Type}, {"F21.0", Int64.Type}, {"F23.0", Int64.Type}, {"F24.0", Int64.Type}})
in
    #"Changed Type2"

Also please find the file attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

6 REPLIES 6
Super User
Super User

Re: 2 Qualitative information Columns into a X yes/no or count event columns.

Could you Please try this....

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhMNjAwVNJRAmE3IwM9A6VYHSRhI7CwIbKwEVw1irAxdkOM4YYYowsbg4VNkIVNUM2OBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Patient = _t, #"Diagnoses position" = _t, #"Diagnoses code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Patient", type text}, {"Diagnoses position", Int64.Type}, {"Diagnoses code", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Diagnoses code"]), "Diagnoses code", "Diagnoses position"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"F20.0", "F21.0", "F23.0", "F24.0"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "F20.0 Flag", each if [F20.0]>0 then "Yes" else "No"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "F21.0 Flag", each if [F21.0]>0 then "Yes" else "No"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "F23.0 Flag", each if [F23.0]>0 then "Yes" else "No"),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "F24.0 Flag", each if [F24.0]>0 then "Yes" else "No"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"F20.0", "F21.0", "F23.0", "F24.0"})
in
    #"Removed Columns"

image.pngInputimage.pngOutput



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

Community Support Team
Community Support Team

Re: 2 Qualitative information Columns into a X yes/no or count event columns.

Hi @areymejias,

 

Sample for your reference.

 

Capture.PNG

 

M code in Advanced editor.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhMNjAwVNJRAmE3IwM9A6VYHSRhI7CwIbKwEVw1irAxdkOM4YYYowsbg4VNkIVNUM2OBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Patient = _t, #"Diagnoses position" = _t, #"Diagnoses code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Patient", type text}, {"Diagnoses position", Int64.Type}, {"Diagnoses code", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Patient]), "Patient", "Diagnoses position", List.Count),
    #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Diagnoses code", type text}, {"F20.0", Int64.Type}, {"F21.0", Int64.Type}, {"F23.0", Int64.Type}, {"F24.0", Int64.Type}})
in
    #"Changed Type2"

Also please find the file attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

areymejias Frequent Visitor
Frequent Visitor

Re: 2 Qualitative information Columns into a X yes/no or count event columns.

It works perfectly, thanks a lot for the fast reply. Nevertheless, It was my fault to see too much unspecific, my question was a simplification because i do have 1.100.000 patients with 10.000 diferent diagnoses, the solution of @PattemManohar and @v-frfei-msft were amazing but too "manual" to make it work with my real data.

 

I do think that i need something like a loop or a function.

 

Thanks a lot,

Angel

Highlighted
areymejias Frequent Visitor
Frequent Visitor

Re: 2 Qualitative information Columns into a X yes/no or count event columns.

It works perfectly, thanks a lot for the fast reply. Nevertheless, It was my fault to see too much unspecific, my question was a simplification because i do have 1.100.000 patients with 10.000 diferent diagnoses, the solution of @PattemManohar and @v-frfei-msft were amazing but too "manual" to make it work with my real data.

 

I do think that i need something like a loop or a function.

 

Thanks a lot,

Angel

Community Support Team
Community Support Team

Re: 2 Qualitative information Columns into a X yes/no or count event columns.

Hi @areymejias,

 

Actually I didn't add custom column or do any manual opearction. All these steps could be done by UI button.

 

2222.png

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Community Support Team
Community Support Team

Re: 2 Qualitative information Columns into a X yes/no or count event columns.

Hi @areymejias,

 

Has your problem been solved ? If any other question, feel free to let me know please.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)