Move column to header/consolidate rows

shellz

New member
Joined
May 26, 2016
Messages
10
Reaction score
0
Points
0
Excel Version(s)
2016
I am having difficulties changing the data layout from this
uniqueNumElement:Text
V20161340968842NFPA/HMIS 1-2
V20166723510435NFPA/HMIS 1-2
V20166723528133NFPA/HMIS 1-2
V20166805482263No Chronic health concerns
V20166805483563No Acute health concerns
V20166805483563No Chronic health concerns
V20170170974086NFPA/HMIS 1-2
V20210182407904NFPA/HMIS 1-2
V20210182407904GHS 3-4
V20210182407904No Acute health concerns
V20210182407904No Chronic health concerns
V20222968581757No Chronic health concerns

to this

uniqueNumNFPA/HMIS 1-2No Chronic health concernsNo Acute health concernsGHS 3-4
V20161340968842Yes
V20166723510435Yes
V20166723528133Yes
V20166805482263 Yes
V20166805483563 YesYes
V20170170974086Yes
V20210182407904YesYesYesYes
V20222968581757 Yes



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

Thank you
 
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
 
Hi shellz!

Code:
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"
 
Last edited:
Thank you! That worked beautifully. I didn't think to add the custom "Yes" column.
 
Back
Top