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
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:
columnA | columnB | desiredColumn |
1 | A | A |
2 | B | B | C |
2 | C | B | C |
3 | D | D | E | F |
3 | E | D | E | F |
3 | F | D | E | F |
4 | G | G |
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
Solved! Go to Solution.
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"
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |