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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Microsoft Employee
Microsoft 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.

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
artemus
Microsoft Employee
Microsoft 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors