Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
list | unique distinct values |
aa,bb,cc | aa |
aa,bb,cc,dd | bb |
aa,ff,gg | cc |
dd | |
ff | |
gg |
Solved! Go to Solution.
Hello @sunik,
We have Split columns with delimiters option in the edit queries of the Power BI:
aa,bb,cc | aa | bb | cc |
bb ,cc | bb | cc |
Once you split , either you can make 3 datasets with different columns using choose "column options" and then append the three datasets (Remember the name of the column which you would append would remain the same .
Dataset1 | dataset 2 | dataset 3 | ||
aa | bb | cc | ||
bb | cc |
After appending data set , you shall get
aa |
bb |
bb |
cc |
cc |
Then you can use remove duplicates in the "Remove Column" dropdown.
This would work 🙂
Regards
Alternatively, you can use Text.Split.
I created the step #"Trimed Text" in the code below by first chosing "Transform - Format - Trim" and then adjusted the code from Text.Trim to Text.Split.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type text}}), #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{},each Text.Split(_,",")), #"Expanded List" = Table.ExpandListColumn(#"Trimmed Text", "List"), #"Removed Duplicates" = Table.Distinct(#"Expanded List") in #"Removed Duplicates"
Hi @sunik,
Have you tried the solutions provided above? Do they work in your scenario? If you still have any question, feel free to post it here.
Regards
Hello @sunik,
We have Split columns with delimiters option in the edit queries of the Power BI:
aa,bb,cc | aa | bb | cc |
bb ,cc | bb | cc |
Once you split , either you can make 3 datasets with different columns using choose "column options" and then append the three datasets (Remember the name of the column which you would append would remain the same .
Dataset1 | dataset 2 | dataset 3 | ||
aa | bb | cc | ||
bb | cc |
After appending data set , you shall get
aa |
bb |
bb |
cc |
cc |
Then you can use remove duplicates in the "Remove Column" dropdown.
This would work 🙂
Regards
Alternatively, you can use Text.Split.
I created the step #"Trimed Text" in the code below by first chosing "Transform - Format - Trim" and then adjusted the code from Text.Trim to Text.Split.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type text}}), #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{},each Text.Split(_,",")), #"Expanded List" = Table.ExpandListColumn(#"Trimmed Text", "List"), #"Removed Duplicates" = Table.Distinct(#"Expanded List") in #"Removed Duplicates"
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |