Forum Discussion

bhanes22's avatar
bhanes22
Helper I
5 years ago
Solved

Pull Distinct Values from concatenated column

I have this column in my table called specifications and it is set up so that each specification is delimited by a comma so it might be set up like so: 

SpecA,SpecB,SpecD

SpecB,SpecX,SpecD,

SpecY,SpecA,SpecB

etc. 

What I want is to transform this or create another column that gives me one one instance of each spec, so like: 

Spec A

Spec B

Spec C

Spec D

... and so on

I'm like 99% sure this can be done, and I even did it before apparentally but now for some reason in query editor its throwing an error so maybe I did it wrong? Idk what's going on with this column on my end, but it would be nice to see how someone more versed with Bi would accomplish this 

Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey bhanes22 ,

     

    yes, that's possible in Power Query. For that go to Transform --> Split Column and in the advanced options choose "Rows":

     

    Afterwards you have to remove the duplicates and then the data is like you wanted:

     

    This here was my example:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5ITXbUAZFOYNJFKVYHIgrhR6CJRuog6VCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Specs = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Specs", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Specs", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Specs"),
    #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter")
in
    #"Removed Duplicates"

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi bhanes22,

    Did selimovd 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements to find it more quickly.

    If these also don't help, please share more detailed information to help us clarify your scenario to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey bhanes22 ,

     

    yes, that's possible in Power Query. For that go to Transform --> Split Column and in the advanced options choose "Rows":

     

    Afterwards you have to remove the duplicates and then the data is like you wanted:

     

    This here was my example:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5ITXbUAZFOYNJFKVYHIgrhR6CJRuog6VCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Specs = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Specs", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Specs", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Specs"),
    #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter")
in
    #"Removed Duplicates"

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis