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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
alameda
New Member

Conditional column which merges all coincident values

Hi:

I'm trying to get a conditional column which merges all the values from the column B which value in the column A is the same.

For example, mi desiredColumn would be something like this:

columnAcolumnBdesiredColumn
1AA
2BB | C
2CB | C
3DD | E | F
3ED | E | F
3FD | E | F
4GG

 

I've been trying to work in something like this, but I don't know how to continue

let
    Table = Table.FromRecords({[columnA = 1, columnB = "A"], [columnA = 2, columnB = "B"], [columnA = 2, columnB = "C"], [columnA = 3, columnB = "D"], [columnA = 3, columnB = "E"], [columnA = 3, columnB = "F"], [columnA = 4, columnB = "G"]}),
    #"Group rows" = Table.Group(Table, {"columnA"}, {{"All", each _, type table [columnA=number, columnB=text]}, {"Distinct", each Table.RowCount(Table.Distinct(_)), type number}}),
    #"Expand All" = Table.ExpandTableColumn(#"Group rows", "All", {"columnB"}, {"All.columnB"}),
    #"Conditional column" = Table.AddColumn(#"Expand All", "desiredColumn", each if [Distinct] = 1 then [All.columnB] else "????")
in
    #"Conditional column"

 

 

 

Any help will be appreciated.

César

1 ACCEPTED SOLUTION
artemus
Employee
Employee

You can do it more simply like:

let
    Source = #table(type table [columnA=Int32.Type, columnB=text], {{1, "A"}, {2, "B"}, {2, "C"}, {3, "D"}, {3, "E"}, {3, "F"}, {4, "G"}}),
    #"Added Custom" = Table.AddColumn(Source, "desiredColumn", each Text.Combine(Table.SelectRows(Source, (x) => [columnA] = x[columnA])[columnB], "|"))
in
    #"Added Custom"

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Well, if it doesn't have to be Power Query, then the DAX is:

DAX Column = 
    VAR __Table = FILTER(ALL('Table'),'Table'[columnA]=EARLIER('Table'[columnA]))
RETURN
    CONCATENATEX(__Table,[columnB]," | ")

 

If it has to be Power Query, @ImkeF can likely tell you how to do it. I attached a PBIX.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
artemus
Employee
Employee

You can do it more simply like:

let
    Source = #table(type table [columnA=Int32.Type, columnB=text], {{1, "A"}, {2, "B"}, {2, "C"}, {3, "D"}, {3, "E"}, {3, "F"}, {4, "G"}}),
    #"Added Custom" = Table.AddColumn(Source, "desiredColumn", each Text.Combine(Table.SelectRows(Source, (x) => [columnA] = x[columnA])[columnB], "|"))
in
    #"Added Custom"

It works!!! 😊😊😊

Thank you so much.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors