Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
areymejias
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
PattemManohar
Community Champion
Community Champion

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"

InputInputOutputOutput





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

Proud to be a PBI Community Champion




View solution in original post

v-frfei-msft
Community Support
Community Support

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
v-frfei-msft
Community Support
Community Support

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.
PattemManohar
Community Champion
Community Champion

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"

InputInputOutputOutput





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

Proud to be a PBI Community Champion




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

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

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.

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors