Forum Discussion
List unique items in table
Hi,
I am trying to replicate something in Power BI that was easy to create in Excel. I have a list of items and want to show for each year what unique items exist in that list. Example date is:
| Year | Value |
| 2021 | A |
| 2021 | A |
| 2021 | B |
| 2021 | C |
| 2021 | C |
| 2021 | C |
| 2022 | A |
| 2022 | D |
| 2022 | D |
| 2023 | C |
| 2023 | C |
| 2023 | E |
| 2023 | F |
| 2023 | G |
| 2023 | G |
| 2023 | H |
| 2024 | H |
| 2024 | H |
| 2024 | I |
| 2024 | J |
Using formula =UNIQUE(FILTER($G$7:$G$26;$F$7:$F$26=J$6;TRUE)) in Excel I get the desired result:
| 2021 | 2022 | 2023 | 2024 |
| A | A | C | H |
| B | D | E | I |
| C | F | J | |
| G | |||
| H |
How can I create something similar in Power BI. The total list contains about 30.000 items with 0 - 50 unique items per year.
Hi Richard_D ,
1. Change the Year and Values column type to Text.
2.Right-click the blank field -->choose New Query-->to create a Blank Query
3.Open the Advanced Editor dialog, enter the following formula:
(Source as table ,ColToPivot as text ,ColForValues as text)=> let PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))) ,#"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _) ,TableFromRecordOfLists = (rec as record, fieldnames as list) => let PartialRecord = Record.SelectFields(rec,fieldnames) ,RecordToList = Record.ToList(PartialRecord) ,Table = Table.FromColumns(RecordToList,fieldnames) in Table ,#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)) ,#"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames) ,#"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames) in #"Expanded Values"4.Enter parematers:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
You can concatenate the values like this:
UniqueValues = CONCATENATEX ( VALUES ( Table1[Value] ), Table1[Value], UNICHAR(10) )It may look better horizontally (use ", " or something instead of UNICHAR(10) as the separator).
Hi,
You can try somthing like this in DAX, many solutions in M as well :
CONCATENATEX(DISTINCT('Tb_Valeur_Uniques'),'Tb_Valeur_Uniques'[Value],UNICHAR(10))CONCATENATEX put the value together,DISTINCT to avoid duplicatesUNICHAR(10) to make line feed between values.Hope it helpsHi Richard_D ,
1. Change the Year and Values column type to Text.
2.Right-click the blank field -->choose New Query-->to create a Blank Query
3.Open the Advanced Editor dialog, enter the following formula:
(Source as table ,ColToPivot as text ,ColForValues as text)=> let PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))) ,#"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _) ,TableFromRecordOfLists = (rec as record, fieldnames as list) => let PartialRecord = Record.SelectFields(rec,fieldnames) ,RecordToList = Record.ToList(PartialRecord) ,Table = Table.FromColumns(RecordToList,fieldnames) in Table ,#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)) ,#"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames) ,#"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames) in #"Expanded Values"4.Enter parematers:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
