Move column to header/consolidate rows

[shellz]

I am having difficulties changing the data layout from this

uniqueNum	Element:Text
V20161340968842	NFPA/HMIS 1-2
V20166723510435	NFPA/HMIS 1-2
V20166723528133	NFPA/HMIS 1-2
V20166805482263	No Chronic health concerns
V20166805483563	No Acute health concerns
V20166805483563	No Chronic health concerns
V20170170974086	NFPA/HMIS 1-2
V20210182407904	NFPA/HMIS 1-2
V20210182407904	GHS 3-4
V20210182407904	No Acute health concerns
V20210182407904	No Chronic health concerns
V20222968581757	No Chronic health concerns

to this

uniqueNum	NFPA/HMIS 1-2	No Chronic health concerns	No Acute health concerns	GHS 3-4
V20161340968842	Yes
V20166723510435	Yes
V20166723528133	Yes
V20166805482263		Yes
V20166805483563		Yes	Yes
V20170170974086	Yes
V20210182407904	Yes	Yes	Yes	Yes
V20222968581757		Yes

I've tried grouping, pivoting but not getting the results I'm looking for. Any suggestions?

Thank you

 

[shellz]

This what I did do but wondering if there is a better/simpler way to do it?

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"uniqueNum"}, {{"TAGHealth", each Text.Combine([#"Element:Text"], ";"), type text}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "NFPA/HMIS 1-2", each if Text.Contains([TAGHealth],"NFPA/HMIS 1-2") then
"Yes"
else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "GHS 3-4", each if Text.Contains([TAGHealth], "GHS 3-4") then
"Yes"
else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "No Acute health concerns", each if Text.Contains([TAGHealth], "No Acute health concerns") then
"Yes"
else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "No Chronic health concerns", each if Text.Contains([TAGHealth], "No Chronic health concerns") then
"Yes"
else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"TAGHealth"})
in
#"Removed Columns"




Thank you

 

[ExcelStarter]

Hi shellz!

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Yes", each "Yes"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"Element:Text"]), "Element:Text", "Yes")
in
    #"Pivoted Column"

 

[shellz]

Thank you! That worked beautifully. I didn't think to add the custom "Yes" column.