Returning unique values and combining the results

[kopilashvili]

Hello,

i'm trying to create a column, which would return one of two value: if there is a value only in one of the columns, then it would retern name of that column, if there are values in more than one column, then it would return different value . For example, for 1st line, as there are values in two columnts Apples and Oranges, the output is combined - "Fruits"; for second line, since there is value only for Apples, the output should be "Apples" 

Index	Apples	Bananas	Oranges	Plums	Output
1	Yes		Yes		Fruits
2	Yes				Apples
3		Yes			Bananas
4	Yes		Yes		Fruits
5				Yes	Plums

[v-alq-msft · Accepted solution]

Hi, @kopilashvili 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column or a measure as below.

Calculated column:

Output Column = 
var tab = {[Apples],[Bananas],[Oranges],[Plums]}
var _count = 
COUNTROWS(
    FILTER(
        tab,
        [Value]="Yes"
    )
)
return
SWITCH(
    TRUE(),
    _count=1,
    SWITCH(
        TRUE(),
        [Apples]="Yes","Apples",
        [Bananas]="Yes","Bananas",
        [Oranges]="Yes","Oranges",
        [Plums]="Yes","Plums"
    ),
    _count>1,"Fruits"
)

Measure:

Output Measure = 
var _apples=SELECTEDVALUE('Table'[Apples])
var _bananas=SELECTEDVALUE('Table'[Bananas])
var _oranges=SELECTEDVALUE('Table'[Oranges])
var _plums=SELECTEDVALUE('Table'[Plums])
var tab = {_apples,_bananas,_oranges,_plums}
var _count = 
COUNTROWS(
    FILTER(
        tab,
        [Value]="Yes"
    )
)
return
SWITCH(
    TRUE(),
    _count=1,
    SWITCH(
        TRUE(),
        _apples="Yes","Apples",
        _bananas="Yes","Bananas",
        _oranges="Yes","Oranges",
        _plums="Yes","Plums"
    ),
    _count>1,"Fruits"
)

Result:

Best Regards

Allan

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

[kopilashvili]

Thank you @v-alq-msft Allan - this worked well for me! 

[Fowmy]

@kopilashvili 

Paste this in a blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYpMLQaSCigst6LSzJJipVidaCUjFAkYdiwoyEmFKDDG0A3CTol5QAhRYULYFlM08yGKAnJKc4HysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Apples = _t, Bananans = _t, Oranges = _t, Plums = _t, Output = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Output"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Index"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "Yes")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Index"}, {{"Count", each _, type table [Index=nullable text, Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if List.Count([Count][Value]) > 1 then "Fruits" else [Count][Attribute]{0}),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Index"}, #"Removed Columns1", {"Index"}, "Removed Columns1", JoinKind.LeftOuter),
    #"Expanded Removed Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns1", {"Custom"}, {"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Removed Columns1",{{"Custom", "New Output"}})
in
    #"Renamed Columns"

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[kopilashvili]

Thank you @Fowmy just curious, if there is a way to solve it without coding / json? 

[Fowmy]

@kopilashvili 

No Code solution :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYpMLQaSCigst6LSzJJipVidaCUjFAkYdiwoyEmFKDDG0A3CTol5QAhRYULYFlM08yGKAnJKc4HysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Apples = _t, Bananans = _t, Oranges = _t, Plums = _t, Output = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Output"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Index"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value] = "Yes" then [Attribute] else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Attribute]), "Attribute", "Custom"),
    #"Inserted Merged Column" = Table.AddColumn(#"Pivoted Column", "Merged", each Text.Combine({[Plums], [Oranges], [Bananans], [Apples]}, "="), type text),
    #"Added Conditional Column1" = Table.AddColumn(#"Inserted Merged Column", "New Output", each if Text.Contains([Merged], "=") then "Fruits" else [Merged]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column1",{"Merged"})
in
    #"Removed Columns2"

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Fowmy]

@kopilashvili 

I need to try and get back to you 🙂

Thanks
Fowmy