Combining Columns in a Table of survey answers

[Anonymous]

Hi all

I'm at the beginning of my Power Bi journey and have been asked to create a Table.

The table what i'm building has 10 columns in but i'm looking to consoildate the below 4 Columns with as below

HouseholdstatusNoneEmployed = True/False

HouseholdstatusOneorMoreDepententChildren = True/False

HouseholdstatusOnlyOneAdult = True/False

HouseholdstatusWithholdInformation = True/False

What i'm looking to do is create a new colum called "HomeSitutation"with the results from the above in the new column but instead of the answers being True/False have the Column names as the answer in the new column

For the above columns in question there can only ever be one True answer as it part of a survey, so if HouseholdstatusWithholdInformation give a True answer the other 3 will be False and ideally the end result I would like to See

Column Header = Home Situtation with the answer as Follows One Per Line for each answer

None Employed 

One or More Depentent Children

Only One Adult

Withhold Information

 Any advice/help would be greatly appricated

Many thanks

[dax]

Hi Missingthepower, 

I am not very clear about your requirement, did you want to get result like below?

If so, you could try below M code in Edit Queries->Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUYoEYj84jtWJVkqC8iJRRJOR1EFkYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, a1 = _t, a2 = _t, a3 = _t, a4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"a1", type text}, {"a2", type text}, {"a3", type text}, {"a4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "Y"))
in
    #"Filtered Rows"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Zoe

Thank you for the below and responding to my query, if I understand it correctly I think the below could work!

I will have amend the naming format to match what I need and let you know

Many Thanks

Ian

[Anonymous]

Hi Zoe

I have recreated the above but hasn't solved my task unfortunately, but thank you any way

Ian

[dax]

Hi Missingthepower,

If possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Above is the columns I would like to Combine into one column, with the end result below with each True answer replace with the column header, there will only be one to True answer per line

Thank you for your help, I hope this helps

[dax · Accepted solution]

Hi Missingthepower,

You could try below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUYpMLQaSfvlIRKxOtFISjA+Rh6sCySWja4DIx8YCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, HouseholdstatusNoneEmplyed = _t, HouseholdstatusOneorMoreDepententChildren = _t, HouseholdstatusOnlyOneAdult = _t, HouseholdstatusWithholdInformation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"HouseholdstatusNoneEmplyed", type text}, {"HouseholdstatusOneorMoreDepententChildren", type text}, {"HouseholdstatusOnlyOneAdult", type text}, {"HouseholdstatusWithholdInformation", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "Yes")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","Householdstatus","",Replacer.ReplaceText,{"Attribute"}),
    #"TRANSFORM" =Table.TransformColumns(#"Replaced Value" ,{{"Attribute",each Text.Combine(Splitter.SplitTextByPositions(Text.PositionOfAny(_, {"A".."Z"},2)) (_), " "), type text}})
in
     #"TRANSFORM"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you Zoe

That has help massively, Ive just created the relationship and it has pulled the data I would expect to see

Have a great weekend

Ian