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

[Anonymous]

Hello everyone,

I do have a datashet like this:

Patient	Diagnoses position	Diagnoses code
Pac001	1	F20.0
Pac001	2	F21.0
Pac002	1	F21.0
Pac003	1	F20.0
Pac003	2	F23.0
Pac003	3	F24.0
Pac004	1	F21.0

 and i do want to obtain this:

	F20.0	F21.0	F23.0	F24.0
Pac001	Yes	Yes	No	No
Pac002	No	Yes	No	No
Pac003	Yes	No	Yes	Yes
Pac004	No	Yes	No	No

Or more preferabily:

	F20.0	F21.0	F23.0	F24.0
Pac001	1	1	0	0
Pac002	0	1	0	0
Pac003	1	0	1	1
Pac004	0	1	0	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

[PattemManohar · Accepted solution]

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"

InputOutput

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Sample for your reference.

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

[PattemManohar · Accepted solution]

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"

InputOutput

[Anonymous]

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

[Anonymous]

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

[v-frfei-msft]

Hi @Anonymous,

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

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

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

Regards,

Frank